Hey, I am getting fascinated towards SQL. REALLY!!!. What a query language!!. A real test for analytical and logical skills. I am planning to go deep into it. From today onwards u may find some posts on SQL and PL/SQL also. PLAN_TABLE IS A VERY useful table that oracle has. Whenever a query is fired with explain plan option plan_table will be automatically updated with the execution plan. Just truncate the table and execute your as… explain plan for Ex: EXPLAIN PLAN FOR SELECT EMPNO, DNAME, MAX(SAL) FROM K_EMP, K_DEPT WHERE K_EMP.DEPTNO = K_DEPT.DEPTNO GROUP BY EMPNO, DNAME This will write the execution plan to plan_table table. SELECT SUBSTR (LPAD(' ', 5*LEVEL-1) OPERATION ' (' OPTIONS ')',1,80 ) "OPERATION", OBJECT_NAME "OBJECT" FROM PLAN_TABLE START WITH ID = 0 CONNECT BY PRIOR ID=PARENT_ID or select LPad(' ', 10*(Level-1)) Level '.' nvl(Position,0) ' ' Operation ' ' Options ' ' Object_Name ' ' Object_Type ' ' Decode(id, 0, Statement_id 'Cost = ' Position) Other ' ' Object_Node "Query Plan" from plan_table start with id = 0 connect by prior id = parent_id This will fetch the execution plan in hierarchical format. Here follows the output. OPERATION OBJECT SELECT STATEMENT () SORT (GROUP BY) HASH JOIN () TABLE ACCESS (FULL) K_DEPT TABLE ACCESS (FULL) K_EMP select LPad(' ', 10*(Level-1)) Level '.' nvl(Position,0) ' ' Operation ' ' Options ' ' Object_Name ' ' Object_Type ' ' Decode(id, 0, Statement_id 'Cost = ' Position) Other ' ' Object_Node "Query Plan" from plan_table start with id = 0 connect by prior id = parent_id THE FOLLOWING QUERY 'ASKS' FOR A PARENT and a supposed child (grand child, grand grand child) and answers, if there is an ancester successor relationship. EXPLAIN PLAN FOR SELECT CASE WHEN COUNT(*) > 0 THEN '&&PARENT IS AN ANCESTOR OF &&CHILD' ELSE '&&PARENT IS NO ANCESTOR OF &&CHILD' END "AND HERE IS THE ANSWER" FROM PARENT_CHILD WHERE CHILD_ = '&&CHILD' start with PARENT_ = '&&PARENT' CONNECT BY PRIOR CHILD_ = PARENT_; SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------- ---------------------------------------------------------------- Id Operation Name Rows ---------------------------------------------------------------- 0 SELECT STATEMENT 1 SORT AGGREGATE * 2 FILTER * 3 CONNECT BY WITH FILTERING 4 NESTED LOOPS * 5 TABLE ACCESS FULL PARENT_CHILD 6 TABLE ACCESS BY USER ROWID PARENT_CHILD 7 NESTED LOOPS 8 BUFFER SORT 9 CONNECT BY PUMP * 10 TABLE ACCESS FULL PARENT_CHILD ---------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("PARENT_CHILD"."CHILD_"='HI') 3 - filter("PARENT_CHILD"."PARENT_"='KARTEEK') 5 - filter("PARENT_CHILD"."PARENT_"='KARTEEK') 10 - filter("PARENT_CHILD"."PARENT_"=NULL) Note: rule based optimization 26 rows selected. I am feeling really good for posting this tech stuff. I try to collect . and read as much as possible on SQL. Karteeek