Northern California Oracle Users Group

Home » Articles posted by Brendan Furey

Author Archives: Brendan Furey

SQL Mini-challenge entry: Outer-Join and Subquery Factors

by Brendan Furey

On my 11.2 XE system, I get 152 buffers for the original query and 31 buffers for Enrique’s query. I also implemented a query similar to Enrique’s but in ANSI syntax, and was surprised to find that initially it gave 33 buffers. I found that changing the order of the component queries gave 31 buffers with very similar 37-line plan to Enrique’s.

Another way of doing it, and with only one SELECT, is using outer-joins. This gave 37 buffers, and was quite a bit shorter, with 21-line plan.

If you relax the no-subquery rule, with the intention of avoiding only the expensive existence subqueries, you could use a subquery factor to do the union on just the constraining tables. This arguably makes the query more readable, and gave 30 buffers, with 28-line plan. I list these two queries with their plans.

Outer-Join Query

SELECT /*+ GATHER_PLAN_STATISTICS OJ_LOC */
l.location_id, l.city
FROM locations l
JOIN departments d
ON d.location_id     = l.location_id
JOIN employees e
ON e.department_id     = d.department_id
LEFT JOIN jobs j
ON j.job_id            = e.job_id
AND j.job_title      = 'President'
LEFT JOIN job_history h
ON h.employee_id    = e.employee_id
LEFT JOIN jobs j2
ON j2.job_id        = h.job_id
AND j2.job_title     = 'President'
WHERE (e.first_name = 'Steven' AND e.last_name = 'King')
OR j.job_id IS NOT NULL
OR j2.job_id IS NOT NULL
/

Outer-Join Plan

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |      1 |00:00:00.01 |      37 |       |       |          |
|*  1 |  FILTER                      |                   |      1 |        |      1 |00:00:00.01 |      37 |       |       |          |
|*  2 |   HASH JOIN RIGHT OUTER      |                   |      1 |    106 |    109 |00:00:00.01 |      37 |  1269K|  1269K|  707K (0)|
|*  3 |    TABLE ACCESS FULL         | JOBS              |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  4 |    HASH JOIN RIGHT OUTER     |                   |      1 |    106 |    109 |00:00:00.01 |      31 |  1134K|  1134K|  765K (0)|
|   5 |     VIEW                     | index$_join$_008  |      1 |     10 |     10 |00:00:00.01 |       6 |       |       |          |
|*  6 |      HASH JOIN               |                   |      1 |        |     10 |00:00:00.01 |       6 |  1096K|  1096K| 1025K (0)|
|   7 |       INDEX FAST FULL SCAN   | JHIST_EMPLOYEE_IX |      1 |     10 |     10 |00:00:00.01 |       3 |       |       |          |
|   8 |       INDEX FAST FULL SCAN   | JHIST_JOB_IX      |      1 |     10 |     10 |00:00:00.01 |       3 |       |       |          |
|*  9 |     HASH JOIN OUTER          |                   |      1 |    106 |    106 |00:00:00.01 |      25 |   858K|   858K| 1243K (0)|
|* 10 |      HASH JOIN               |                   |      1 |    106 |    106 |00:00:00.01 |      18 |  1063K|  1063K| 1253K (0)|
|* 11 |       HASH JOIN              |                   |      1 |     27 |     27 |00:00:00.01 |      12 |  1156K|  1156K| 1131K (0)|
|  12 |        VIEW                  | index$_join$_001  |      1 |     23 |     23 |00:00:00.01 |       6 |       |       |          |
|* 13 |         HASH JOIN            |                   |      1 |        |     23 |00:00:00.01 |       6 |  1023K|  1023K| 1130K (0)|
|  14 |          INDEX FAST FULL SCAN| LOC_CITY_IX       |      1 |     23 |     23 |00:00:00.01 |       3 |       |       |          |
|  15 |          INDEX FAST FULL SCAN| LOC_ID_PK         |      1 |     23 |     23 |00:00:00.01 |       3 |       |       |          |
|  16 |        VIEW                  | index$_join$_002  |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|* 17 |         HASH JOIN            |                   |      1 |        |     27 |00:00:00.01 |       6 |  1096K|  1096K| 1269K (0)|
|  18 |          INDEX FAST FULL SCAN| DEPT_ID_PK        |      1 |     27 |     27 |00:00:00.01 |       3 |       |       |          |
|  19 |          INDEX FAST FULL SCAN| DEPT_LOCATION_IX  |      1 |     27 |     27 |00:00:00.01 |       3 |       |       |          |
|  20 |       TABLE ACCESS FULL      | EMPLOYEES         |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
|* 21 |      TABLE ACCESS FULL       | JOBS              |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

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

1 - filter((("E"."FIRST_NAME"='Steven' AND "E"."LAST_NAME"='King') OR "J"."JOB_ID" IS NOT NULL OR "J2"."JOB_ID" IS NOT
NULL))
2 - access("J2"."JOB_ID"="H"."JOB_ID")
3 - filter("J2"."JOB_TITLE"='President')
4 - access("H"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
6 - access(ROWID=ROWID)
9 - access("J"."JOB_ID"="E"."JOB_ID")
10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
11 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
13 - access(ROWID=ROWID)
17 - access(ROWID=ROWID)
21 - filter("J"."JOB_TITLE"='President')

Subquery Factor Union Query

WITH driving_union AS (
SELECT e.department_id
FROM employees e
WHERE (e.first_name = 'Steven' AND e.last_name = 'King')
UNION
SELECT e.department_id
FROM jobs j
JOIN employees e
ON e.job_id        = j.job_id
WHERE j.job_title     = 'President'
UNION
SELECT e.department_id
FROM jobs j
JOIN job_history h
ON j.job_id        = h.job_id
JOIN employees e
ON e.employee_id    = h.employee_id
WHERE j.job_title     = 'President'
)
SELECT /*+ GATHER_PLAN_STATISTICS SQF_UNION */
l.location_id, l.city
FROM driving_union u
JOIN departments d
ON d.department_id     = u.department_id
JOIN locations l
ON l.location_id     = d.location_id
/

Subquery Factor Union Plan

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |      1 |00:00:00.01 |      30 |       |       |          |
|*  1 |  HASH JOIN                          |                  |      1 |      8 |      1 |00:00:00.01 |      30 |  1517K|  1517K|  667K (0)|
|*  2 |   HASH JOIN                         |                  |      1 |      8 |      1 |00:00:00.01 |      23 |  1517K|  1517K|  726K (0)|
|   3 |    VIEW                             |                  |      1 |      8 |      1 |00:00:00.01 |      17 |       |       |          |
|   4 |     SORT UNIQUE                     |                  |      1 |      8 |      1 |00:00:00.01 |      17 |  2048 |  2048 | 2048  (0)|
|   5 |      UNION-ALL                      |                  |      1 |        |      2 |00:00:00.01 |      17 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID   | EMPLOYEES        |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN             | EMP_NAME_IX      |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       NESTED LOOPS                  |                  |      1 |        |      1 |00:00:00.01 |       8 |       |       |          |
|   9 |        NESTED LOOPS                 |                  |      1 |      6 |      1 |00:00:00.01 |       7 |       |       |          |
|* 10 |         TABLE ACCESS FULL           | JOBS             |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|* 11 |         INDEX RANGE SCAN            | EMP_JOB_IX       |      1 |      6 |      1 |00:00:00.01 |       1 |       |       |          |
|  12 |        TABLE ACCESS BY INDEX ROWID  | EMPLOYEES        |      1 |      6 |      1 |00:00:00.01 |       1 |       |       |          |
|  13 |       NESTED LOOPS                  |                  |      1 |        |      0 |00:00:00.01 |       7 |       |       |          |
|  14 |        NESTED LOOPS                 |                  |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|  15 |         NESTED LOOPS                |                  |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|* 16 |          TABLE ACCESS FULL          | JOBS             |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|  17 |          TABLE ACCESS BY INDEX ROWID| JOB_HISTORY      |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|* 18 |           INDEX RANGE SCAN          | JHIST_JOB_IX     |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|* 19 |         INDEX UNIQUE SCAN           | EMP_EMP_ID_PK    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  20 |        TABLE ACCESS BY INDEX ROWID  | EMPLOYEES        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  21 |    VIEW                             | index$_join$_011 |      1 |     27 |     27 |00:00:00.01 |       6 |       |       |          |
|* 22 |     HASH JOIN                       |                  |      1 |        |     27 |00:00:00.01 |       6 |  1096K|  1096K| 1247K (0)|
|  23 |      INDEX FAST FULL SCAN           | DEPT_ID_PK       |      1 |     27 |     27 |00:00:00.01 |       3 |       |       |          |
|  24 |      INDEX FAST FULL SCAN           | DEPT_LOCATION_IX |      1 |     27 |     27 |00:00:00.01 |       3 |       |       |          |
|  25 |   VIEW                              | index$_join$_013 |      1 |     23 |     23 |00:00:00.01 |       7 |       |       |          |
|* 26 |    HASH JOIN                        |                  |      1 |        |     23 |00:00:00.01 |       7 |  1023K|  1023K| 1127K (0)|
|  27 |     INDEX FAST FULL SCAN            | LOC_CITY_IX      |      1 |     23 |     23 |00:00:00.01 |       3 |       |       |          |
|  28 |     INDEX FAST FULL SCAN            | LOC_ID_PK        |      1 |     23 |     23 |00:00:00.01 |       4 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------

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

1 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
2 - access("D"."DEPARTMENT_ID"="U"."DEPARTMENT_ID")
7 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
10 - filter("J"."JOB_TITLE"='President')
11 - access("E"."JOB_ID"="J"."JOB_ID")
16 - filter("J"."JOB_TITLE"='President')
18 - access("J"."JOB_ID"="H"."JOB_ID")
19 - access("E"."EMPLOYEE_ID"="H"."EMPLOYEE_ID")
22 - access(ROWID=ROWID)
26 - access(ROWID=ROWID)
Advertisements