Author Archives: mohan

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.