Northern California Oracle Users Group

Home » Articles posted by eaviles94

Author Archives: eaviles94

SQL Mini-challenge entry: Enrique Aviles

by Enrique Aviles

This is my entry for the SQL Mini-challenge that was published in the August 2014 issue of the NoCOUG Journal.

The query that needs to be rewritten and optimized without using subqueries is:

SELECT /*+ GATHER_PLAN_STATISTICS */ l.location_id,
 l.city
FROM locations l
WHERE EXISTS
 (SELECT *
 FROM departments d,
 employees e,
 jobs j
 WHERE d.location_id = l.location_id
 AND e.department_id = d.department_id
 AND j.job_id = e.job_id
 AND ( (e.first_name = 'Steven'
 AND e.last_name = 'King')
 OR j.job_title = 'President'
 OR EXISTS
 (SELECT *
 FROM job_history jh,
 jobs j2
 WHERE jh.employee_id = e.employee_id
 AND j2.job_id = jh.job_id
 AND j2.job_title = 'President'
 ) )
 );

The original query generates the following execution plan:

SQL>  select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));
SQL_ID  dpub2npt3z73y, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ l.location_id,   l.city FROM
locations l WHERE EXISTS   (SELECT *   FROM departments d,
employees e,     jobs j   WHERE d.location_id = l.location_id   AND
e.department_id = d.department_id   AND j.job_id        = e.job_id
AND ( (e.first_name = :"SYS_B_0"   AND e.last_name     = :"SYS_B_1")
OR j.job_title      = :"SYS_B_2"   OR EXISTS     (SELECT *     FROM
job_history jh,       jobs j2     WHERE jh.employee_id = e.employee_id
   AND j2.job_id        = jh.job_id     AND j2.job_title     =
:"SYS_B_3"     ) )   )

Plan hash value: 880768015

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |      1 |        |       |    85 (100)|          |      1 |00:00:00.01 |     201 |
|*  1 |  FILTER                          |                   |      1 |        |       |            |          |      1 |00:00:00.01 |     201 |
|   2 |   VIEW                           | index$_join$_001  |      1 |     23 |   276 |     3  (34)| 00:00:01 |     23 |00:00:00.01 |       9 |
|*  3 |    HASH JOIN                     |                   |      1 |        |       |            |          |     23 |00:00:00.01 |       9 |
|   4 |     INDEX FAST FULL SCAN         | LOC_CITY_IX       |      1 |     23 |   276 |     1   (0)| 00:00:01 |     23 |00:00:00.01 |       4 |
|   5 |     INDEX FAST FULL SCAN         | LOC_ID_PK         |      1 |     23 |   276 |     1   (0)| 00:00:01 |     23 |00:00:00.01 |       5 |
|*  6 |   FILTER                         |                   |     23 |        |       |            |          |      1 |00:00:00.01 |     192 |
|*  7 |    HASH JOIN                     |                   |     23 |     12 |   780 |     7   (0)| 00:00:01 |     89 |00:00:00.01 |      94 |
|   8 |     NESTED LOOPS                 |                   |     23 |        |       |            |          |    106 |00:00:00.01 |      49 |
|   9 |      NESTED LOOPS                |                   |     23 |     37 |  1406 |     4   (0)| 00:00:01 |    106 |00:00:00.01 |      38 |
|  10 |       TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |     23 |      4 |    28 |     2   (0)| 00:00:01 |     27 |00:00:00.01 |      30 |
|* 11 |        INDEX RANGE SCAN          | DEPT_LOCATION_IX  |     23 |      4 |       |     1   (0)| 00:00:01 |     27 |00:00:00.01 |      23 |
|* 12 |       INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |     27 |     10 |       |     0   (0)|          |    106 |00:00:00.01 |       8 |
|  13 |      TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |    106 |     10 |   310 |     1   (0)| 00:00:01 |    106 |00:00:00.01 |      11 |
|  14 |     TABLE ACCESS FULL            | JOBS              |      7 |     19 |   513 |     3   (0)| 00:00:01 |    115 |00:00:00.01 |      45 |
|  15 |    NESTED LOOPS                  |                   |     88 |        |       |            |          |      0 |00:00:00.01 |      98 |
|  16 |     NESTED LOOPS                 |                   |     88 |      1 |    40 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |      94 |
|  17 |      TABLE ACCESS BY INDEX ROWID | JOB_HISTORY       |     88 |      1 |    13 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |      91 |
|* 18 |       INDEX RANGE SCAN           | JHIST_EMPLOYEE_IX |     88 |      1 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |      88 |
|* 19 |      INDEX UNIQUE SCAN           | JOB_ID_PK         |      4 |      1 |       |     0   (0)|          |      4 |00:00:00.01 |       3 |
|* 20 |     TABLE ACCESS BY INDEX ROWID  | JOBS              |      4 |      1 |    27 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( IS NOT NULL)
   3 - access(ROWID=ROWID)
   6 - filter((("E"."FIRST_NAME"=:SYS_B_0 AND "E"."LAST_NAME"=:SYS_B_1) OR "J"."JOB_TITLE"=:SYS_B_2 OR  IS NOT NULL))
   7 - access("J"."JOB_ID"="E"."JOB_ID")
  11 - access("D"."LOCATION_ID"=:B1)
  12 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  18 - access("JH"."EMPLOYEE_ID"=:B1)
  19 - access("J2"."JOB_ID"="JH"."JOB_ID")
  20 - filter("J2"."JOB_TITLE"=:SYS_B_3)

53 rows selected.

Below is the new query without subqueries. Both OR conditions were replaced with UNIONs and there are no subqueries.

SELECT /*+ GATHER_PLAN_STATISTICS */
     l.location_id, l.city
FROM locations l,
     departments d,
     employees e,
     jobs j
WHERE d.location_id = l.location_id
AND e.department_id = d.department_id
AND j.job_id        = e.job_id
AND e.first_name = 'Steven' AND e.last_name     = 'King'
UNION
SELECT /*+ GATHER_PLAN_STATISTICS */
     l.location_id, l.city
FROM locations l,
     departments d,
     employees e,
     jobs j
WHERE d.location_id = l.location_id
AND e.department_id = d.department_id
AND j.job_id        = e.job_id
AND  j.job_title      = 'President'
UNION
SELECT /*+ GATHER_PLAN_STATISTICS */
     l.location_id, l.city
FROM locations l,
     departments d,
     employees e,
     jobs j,
     job_history jh
WHERE d.location_id = l.location_id
AND e.department_id = d.department_id
AND j.job_id        = e.job_id
AND j.job_title     = 'President'
AND jh.employee_id  = e.employee_id
AND j.job_id        = jh.job_id

The new query generates the following execution plan:

SQL>  select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));
SQL_ID  d7qnzyh5h3290, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */      l.location_id, l.city FROM
locations l,      departments d,      employees e,      jobs j WHERE
d.location_id = l.location_id AND e.department_id = d.department_id AND
j.job_id        = e.job_id AND e.first_name = :"SYS_B_0" AND
e.last_name     = :"SYS_B_1" UNION SELECT /*+ GATHER_PLAN_STATISTICS */
     l.location_id, l.city FROM locations l,      departments d,
employees e,      jobs j WHERE d.location_id = l.location_id AND
e.department_id = d.department_id AND j.job_id        = e.job_id AND
j.job_title      = :"SYS_B_2" UNION SELECT /*+ GATHER_PLAN_STATISTICS
*/      l.location_id, l.city FROM locations l,      departments d,
 employees e,      jobs j,      job_history jh WHERE d.location_id =
l.location_id AND e.department_id = d.department_id AND j.job_id
= e.job_id AND j.job_title     = :"SYS_B_3" AND jh.employee_id  =
e.employee_id AND j.job_id        = jh.job_id

Plan hash value: 4023299366

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |      1 |        |       |    23 (100)|          |      1 |00:00:00.01 |      36 |
|   1 |  SORT UNIQUE                        |                    |      1 |      8 |   250 |    23  (70)| 00:00:01 |      1 |00:00:00.01 |      36 |
|   2 |   UNION-ALL                         |                    |      1 |        |       |            |          |      1 |00:00:00.01 |      36 |
|   3 |    NESTED LOOPS                     |                    |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |
|   4 |     NESTED LOOPS                    |                    |      1 |      1 |    75 |     7   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |
|   5 |      NESTED LOOPS                   |                    |      1 |      1 |    63 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |
|   6 |       NESTED LOOPS                  |                    |      1 |      1 |    56 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |
|   7 |        NESTED LOOPS                 |                    |      1 |      1 |    40 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |
|*  8 |         TABLE ACCESS FULL           | JOBS               |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|   9 |         TABLE ACCESS BY INDEX ROWID | JOB_HISTORY        |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 10 |          INDEX RANGE SCAN           | JHIST_JOB_IX       |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|* 11 |        TABLE ACCESS BY INDEX ROWID  | EMPLOYEES          |      0 |      1 |    16 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 12 |         INDEX UNIQUE SCAN           | EMP_EMP_ID_PK      |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  13 |       TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS        |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 14 |        INDEX UNIQUE SCAN            | DEPT_ID_PK         |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|* 15 |      INDEX UNIQUE SCAN              | LOC_ID_PK          |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  16 |     TABLE ACCESS BY INDEX ROWID     | LOCATIONS          |      0 |      1 |    12 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 17 |    HASH JOIN                        |                    |      1 |      7 |   175 |    14  (15)| 00:00:01 |      1 |00:00:00.01 |      28 |
|  18 |     VIEW                            | VW_JF_SET$8D92B5DA |      1 |      7 |    91 |    11  (10)| 00:00:01 |      1 |00:00:00.01 |      20 |
|  19 |      SORT UNIQUE                    |                    |      1 |      7 |   301 |    11  (37)| 00:00:01 |      1 |00:00:00.01 |      20 |
|  20 |       UNION-ALL                     |                    |      1 |        |       |            |          |      2 |00:00:00.01 |      20 |
|  21 |        NESTED LOOPS                 |                    |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |
|  22 |         NESTED LOOPS                |                    |      1 |      1 |    25 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|  23 |          TABLE ACCESS BY INDEX ROWID| EMPLOYEES          |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 24 |           INDEX RANGE SCAN          | EMP_NAME_IX        |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|* 25 |          INDEX UNIQUE SCAN          | DEPT_ID_PK         |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  26 |         TABLE ACCESS BY INDEX ROWID | DEPARTMENTS        |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|* 27 |        HASH JOIN                    |                    |      1 |      6 |   276 |     8  (13)| 00:00:01 |      1 |00:00:00.01 |      16 |
|  28 |         NESTED LOOPS                |                    |      1 |        |       |            |          |      1 |00:00:00.01 |       9 |
|  29 |          NESTED LOOPS               |                    |      1 |      6 |   234 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|* 30 |           TABLE ACCESS FULL         | JOBS               |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|* 31 |           INDEX RANGE SCAN          | EMP_JOB_IX         |      1 |      6 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  32 |          TABLE ACCESS BY INDEX ROWID| EMPLOYEES          |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  33 |         TABLE ACCESS FULL           | DEPARTMENTS        |      1 |     27 |   189 |     3   (0)| 00:00:01 |     27 |00:00:00.01 |       7 |
|  34 |     VIEW                            | index$_join$_001   |      1 |     23 |   276 |     3  (34)| 00:00:01 |     23 |00:00:00.01 |       8 |
|* 35 |      HASH JOIN                      |                    |      1 |        |       |            |          |     23 |00:00:00.01 |       8 |
|  36 |       INDEX FAST FULL SCAN          | LOC_CITY_IX        |      1 |     23 |   276 |     1   (0)| 00:00:01 |     23 |00:00:00.01 |       4 |
|  37 |       INDEX FAST FULL SCAN          | LOC_ID_PK          |      1 |     23 |   276 |     1   (0)| 00:00:01 |     23 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - filter("J"."JOB_TITLE"=:SYS_B_3)
  10 - access("J"."JOB_ID"="JH"."JOB_ID")
  11 - filter("J"."JOB_ID"="E"."JOB_ID")
  12 - access("JH"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
  14 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  15 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
  17 - access("ITEM_1"="L"."LOCATION_ID")
  24 - access("E"."LAST_NAME"=:SYS_B_1 AND "E"."FIRST_NAME"=:SYS_B_0)
  25 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  27 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  30 - filter("J"."JOB_TITLE"=:SYS_B_2)
  31 - access("J"."JOB_ID"="E"."JOB_ID")
  35 - access(ROWID=ROWID)

Both queries generate the same output:

LOCATION_ID CITY
———– ——————————
1700 Seattle

Notice the number of buffer gets was reduced from 201 in the original query to 36 in the new query.