Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge entry: Outer-Join and Subquery Factors

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

1 Comment

  1. nocoug says:

    There was a little ambiguity in the problem statement. To avoid ambiguity, the wording should have been “the challenge is to rewrite the above query without testing of existence in a set using subqueries” because the preamble indicated that the goal was to avoid existence testing using techniques such as EXISTS, IN, ALL, ANY, ALL, and SOME. Inline views are therefore permissible. Anything not explicitly disallowed by the problem statement is also permissible.

    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: