External SQL Rewriters 
by Richard To and Cara Pang


Listing One

SELECT * 
 FROM A
 WHERE A.C1 IN (SELECT B.C1 
                 FROM B
                 WHERE EXISTS (SELECT 'x' 
                                FROM C
                                WHERE B.C2 = C.C2 ))

Listing Two
SELECT * 
  FROM grade A 
 WHERE A.grd_id IN (SELECT B.emp_grade 
                      FROM employee B 
                     WHERE EXISTS (SELECT 'x' 
                                     FROM department C
                                    WHERE B.emp_dept = C.dpt_id))

Listing Three

EXECUTION PLAN :
[-] SELECT STATEMENT CHOOSE Cost = 
                         24847 (Cost = 24847 Cardinality = 61 Bytes = 3111 )
    [-] HASH JOIN (Cost = 24847 Cardinality = 61 Bytes = 3111 )
        [-] VIEW SYS.VW_NSO_1 (Cost = 24845 Cardinality = 45 Bytes = 585 )
            [-] SORT UNIQUE (Cost = 24845 Cardinality = 45 Bytes = 495 )
                [-] NESTED LOOPS 
                        (Cost = 6072 Cardinality = 3999700 Bytes = 43996700 )
                    [-] PARTITION RANGE ALL ( Partition Start = 
                                     1 Partition Stop = 14 Partition ID = 5)
                        [ ] TABLE ACCESS FULL SQLEXP.EMPLOYEE ANALYZED 
                                 (Cost = 6072 Cardinality = 3999700 Bytes = 
                                      27997900 Partition Start = 1 Partition 
                                                   Stop = 14 Partition ID = 5)
                    [ ] INDEX UNIQUE SCAN SQLEXP.DPT_ID_INX ANALYZED 
                                           ( Cardinality = 430 Bytes = 1720 )
        [ ] TABLE ACCESS FULL SQLEXP.GRADE ANALYZED 
                                     (Cost = 1 Cardinality = 61 Bytes = 2318 )


Listing Four

EXECUTION PLAN :
[-] SELECT STATEMENT CHOOSE Cost = 1 (Cost = 1 Cardinality = 4 Bytes = 152 )
    [-] FILTER ( )
        [ ] TABLE ACCESS FULL SQLEXP.GRADE ANALYZED 
                                      (Cost = 1 Cardinality = 4 Bytes = 152 )
        [-] NESTED LOOPS (Cost = 92709 Cardinality = 88883 Bytes = 977713 )
            [-] TABLE ACCESS BY GLOBAL INDEX ROWID SQLEXP.EMPLOYEE ANALYZED 
                              (Cost = 3826 Cardinality = 88883 Bytes = 622181 
                               Partition Start = ROW LOCATION Partition Stop =
                               ROW LOCATION Partition ID = 4)
                [ ] INDEX RANGE SCAN SQLEXP.EMP_GRADE_IDX ANALYZED 
                                            (Cost = 225 Cardinality = 88883 )
            [ ] INDEX FULL SCAN SQLEXP.DPT_ID_INX ANALYZED 
                                    (Cost = 1 Cardinality = 430 Bytes = 1720 )


Listing Five

SELECT * 
 FROM grade A 
 WHERE EXISTS (SELECT 'X' 
                 FROM employee b 
                 WHERE B.emp_dept IN (SELECT C.dpt_id || '' 
FROM department c) 
                 AND B.emp_grade = A.grd_id)






2

