Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge entry: Enrique Aviles

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.

Advertisements

3 Comments

  1. Enrique,

    Are you sure you want line 32 in your new query? Line 32 combined with line 35 states that the employee should be both currently a President as well as historically 😉

    (Not that it matters for the result with the HR data and it doesn’t change the buffer gets either, but with other data your query might return different output than the original.)

    Regards
    Kim Berg Hansen

    Like

  2. Hello,

    I am afraid there is an error in the last query block : the job table shouldn’t be joined to the employee table, only to the job_history table. Here we want the employees who used to be president, not the ones who are currently president. So I think the following condition AND j.job_id = e.job_id should be removed.

    Regards,

    Emmanuel

    Like

  3. eaviles94 says:

    Kim, Emmanuel,

    Good catch! I missed that part on the last query.Thanks for pointing it out.

    Enrique

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Next NoCOUG meeting
August 20 at Chevron, San Ramon

Follow me on Twitter

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 280 other followers

%d bloggers like this: