Category Archives: Uncategorized
Writing SQL queries Also see How to debug PL/SQL code Transform a subquery involving the IN clause to a Join? Original Statement: SELECT e.empno, e.ename FROM emp e WHERE e.deptno IN ( SELECT d.deptno FROM dept d WHERE d.loc = ‘CHICAGO’ ); New Statement: SELECT e.empno, e.ename FROM emp e, dept d WHERE d.loc = [...]
How to handle data in BULK using BULK collection and BULK DML, using global temporary tables and PL/SQL indexed tables etc
Dealing with Constraints Introduction Three type of constraints are discussed here. ie Primary key, Unique and Referential A Foreign key constraint has to refer to a Primary key constraint of another table The constraint_type in user_constraints table has value P for Primary key, U for Unique and R for Referential The following code works for [...]
Oracle System Views Source code for Oracle SYS Views
Hierarchial Queries The queries shown below demonstrates how to query an hierarchial database. The queries works on employees tables. The script for creating the table and inserting data is available here…
Choosing indexes, how to select indexing columns, whether to compress index or not, rebuilding indexes etc is discussed.
Oracle System Views Also see How to debug PL/SQL code
Oracle Installation Links for Windows, Unix, Linux and Solaris platforms
About the pros and cons of indexes and explains about how indexing strategies for tables huge amount of data.