Northern California Oracle Users Group

Home » Articles posted by cmartin2

Author Archives: cmartin2

SQL Mini-challenge entry: Craig Martin v4

by Craig Martin

So from my last post Brendan Furey pointed out one minor detail.. that my code was seriously flawed. It only returned the results from one of the criteria found. Picky, picky 🙂 So this is my last effort.. Back up to 12 buffers, but still works when you change the parameters:

select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id, l.city
from dual
    left outer join jobs j
        on j.job_title = 'President'
    left outer join job_history jh
        on jh.job_id = j.job_id
    left outer join employees e
        on (e.first_name = 'Steven' and e.last_name = 'King')
    left outer join employees e2
        on e2.job_id = j.job_id
        and e2.rowid <> e.rowid
    left outer join employees e3
        on e3.employee_id = jh.employee_id
        and e3.rowid <> e.rowid
        and e3.rowid <> e2.rowid
    inner join employees e4
        on e4.rowid = e.rowid
        or e4.rowid = e2.rowid
        or e4.rowid = e3.rowid
    inner join departments d
        on d.department_id = e4.department_id
    inner join locations l
        on d.location_id = l.location_id;
PLAN_TABLE_OUTPUT
SQL_ID  0rt22nxkn3b1w, child number 0
-------------------------------------
select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) 
use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id, 
l.city from dual     left outer join jobs j         on j.job_title = 
'Sales Manager'     left outer join job_history jh         on jh.job_id 
= j.job_id     left outer join employees e         on (e.first_name = 
'Steven' and e.last_name = 'King')     left outer join employees e2     
    on e2.job_id = j.job_id         and e2.rowid <> e.rowid     left 
outer join employees e3         on e3.employee_id = jh.employee_id      
   and e3.rowid <> e.rowid         and e3.rowid <> e2.rowid     inner 
join employees e4         on e4.rowid = e.rowid         or e4.rowid = 
e2.rowid         or e4.rowid = e3.rowid     inner join departments d    
     on d.department_id = e4.department_id     inner join locations l   
      on d.location_id = l.location_id
 
Plan hash value: 267334121
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |      1 |        |       |    46 (100)|          |      2 |00:00:00.01 |      61 |      2 |
|   1 |  HASH UNIQUE                             |                   |      1 |     18 |  1404 |    46   (3)| 00:00:01 |      2 |00:00:00.01 |      61 |      2 |
|   2 |   NESTED LOOPS                           |                   |      1 |        |       |            |          |     15 |00:00:00.01 |      61 |      2 |
|   3 |    NESTED LOOPS                          |                   |      1 |     18 |  1404 |    45   (0)| 00:00:01 |     15 |00:00:00.01 |      46 |      2 |
|   4 |     NESTED LOOPS                         |                   |      1 |     18 |  1188 |    27   (0)| 00:00:01 |     15 |00:00:00.01 |      42 |      2 |
|   5 |      NESTED LOOPS                        |                   |      1 |     18 |  1062 |     9   (0)| 00:00:01 |     15 |00:00:00.01 |      23 |      2 |
|   6 |       NESTED LOOPS OUTER                 |                   |      1 |      6 |   264 |     6   (0)| 00:00:01 |      5 |00:00:00.01 |       8 |      2 |
|   7 |        VIEW                              |                   |      1 |      6 |   168 |     6   (0)| 00:00:01 |      5 |00:00:00.01 |       6 |      1 |
|   8 |         NESTED LOOPS OUTER               |                   |      1 |      6 |   318 |     6   (0)| 00:00:01 |      5 |00:00:00.01 |       6 |      1 |
|   9 |          VIEW                            |                   |      1 |      1 |    32 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      1 |
|  10 |           MERGE JOIN OUTER               |                   |      1 |      1 |    32 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      1 |
|  11 |            NESTED LOOPS OUTER            |                   |      1 |      1 |    20 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      1 |
|  12 |             NESTED LOOPS OUTER           |                   |      1 |      1 |     7 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|  13 |              FAST DUAL                   |                   |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |      0 |
|  14 |              VIEW                        |                   |      1 |      1 |     7 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 15 |               TABLE ACCESS BY INDEX ROWID| JOBS              |      1 |      1 |    27 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|  16 |                INDEX FULL SCAN           | JOB_ID_PK         |      1 |     19 |       |     1   (0)| 00:00:01 |     19 |00:00:00.01 |       1 |      0 |
|  17 |             TABLE ACCESS BY INDEX ROWID  | JOB_HISTORY       |      1 |      1 |    13 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 |
|* 18 |              INDEX RANGE SCAN            | JHIST_JOB_IX      |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  19 |            BUFFER SORT                   |                   |      1 |      1 |    12 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  20 |             VIEW                         |                   |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|* 21 |              INDEX RANGE SCAN            | EMP_NAME_IX       |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|* 22 |          INDEX RANGE SCAN                | EMP_JOB_IX        |      1 |      6 |   126 |     0   (0)|          |      5 |00:00:00.01 |       1 |      0 |
|* 23 |        INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK     |      5 |      1 |    16 |     0   (0)|          |      5 |00:00:00.01 |       2 |      1 |
|* 24 |       INDEX FAST FULL SCAN               | EMP_DEPARTMENT_IX |      5 |      3 |    45 |     1   (0)| 00:00:01 |     15 |00:00:00.01 |      15 |      0 |
|  25 |      TABLE ACCESS BY INDEX ROWID         | DEPARTMENTS       |     15 |      1 |     7 |     1   (0)| 00:00:01 |     15 |00:00:00.01 |      19 |      0 |
|* 26 |       INDEX UNIQUE SCAN                  | DEPT_ID_PK        |     15 |      1 |       |     0   (0)|          |     15 |00:00:00.01 |       4 |      0 |
|* 27 |     INDEX UNIQUE SCAN                    | LOC_ID_PK         |     15 |      1 |       |     0   (0)|          |     15 |00:00:00.01 |       4 |      0 |
|  28 |    TABLE ACCESS BY INDEX ROWID           | LOCATIONS         |     15 |      1 |    12 |     1   (0)| 00:00:01 |     15 |00:00:00.01 |      15 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  15 - filter("J"."JOB_TITLE"='Sales Manager')
  18 - access("JH"."JOB_ID"="J"."JOB_ID")
  21 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  22 - access("E2"."JOB_ID"="J"."JOB_ID")
       filter("E2".ROWID<>"E"."ROWID")
  23 - access("E3"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
       filter(("E3".ROWID<>"E2"."ROWID" AND "E3".ROWID<>"E"."ROWID"))
  24 - filter(("E4".ROWID="E"."QCSJ_C000000000900017" OR "E4".ROWID="E2"."QCSJ_C000000000900016" OR "E4".ROWID="E3".ROWID))
  26 - access("D"."DEPARTMENT_ID"="E4"."DEPARTMENT_ID")
  27 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Thanks again to the NoCOUG for putting on this challenge!

Advertisements

SQL Mini-challenge entry: Craig Martin v3

by Craig Martin

Blasted.. after looking at the solution from Jimmy Brock, I see that adding one more hint to use index fast full scan to get to the Jobs row gets me down to 10 buffers. Great addition! So my newest solution is:

select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id, l.city
from dual
    left outer join jobs j
        on j.job_title = 'President'
    left outer join job_history jh
        on jh.job_id = j.job_id
    left outer join employees e
        on (e.first_name = 'Steven' and e.last_name = 'King')
    left outer join employees e2
        on e2.job_id = j.job_id
        and e2.rowid <> e.rowid
    left outer join employees e3
        on e3.employee_id = jh.employee_id
        and e3.rowid <> e.rowid
        and e3.rowid <> e2.rowid
    inner join departments d
        on d.department_id = coalesce(e.department_id, e2.department_id, e3.department_id)
    inner join locations l
        on d.location_id = l.location_id;
PLAN_TABLE_OUTPUT
SQL_ID  a10fz9skz3pnz, child number 1
-------------------------------------
select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e)
use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id,
l.city from dual     left outer join jobs j         on j.job_title =
'President'     left outer join job_history jh         on jh.job_id =
j.job_id     left outer join employees e         on (e.first_name =
'Steven' and e.last_name = 'King')     left outer join employees e2
    on e2.job_id = j.job_id         and e2.rowid <> e.rowid     left
outer join employees e3         on e3.employee_id = jh.employee_id
   and e3.rowid <> e.rowid         and e3.rowid <> e2.rowid     inner
join departments d         on d.department_id =
coalesce(e.department_id, e2.department_id, e3.department_id)     inner
join locations l         on d.location_id = l.location_id

Plan hash value: 1725752989

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |      1 |        |       |    27 (100)|          |      1 |00:00:00.01 |      10 |
|   1 |  HASH UNIQUE                            |               |      1 |      6 |   552 |    27   (4)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   2 |   NESTED LOOPS                          |               |      1 |        |       |            |          |      1 |00:00:00.01 |      10 |
|   3 |    NESTED LOOPS                         |               |      1 |      6 |   552 |    26   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|   4 |     NESTED LOOPS                        |               |      1 |      6 |   480 |    20   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|   5 |      NESTED LOOPS OUTER                 |               |      1 |      6 |   438 |    14   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|   6 |       VIEW                              |               |      1 |      6 |   324 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|   7 |        NESTED LOOPS OUTER               |               |      1 |      6 |   414 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|   8 |         VIEW                            |               |      1 |      1 |    45 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   9 |          MERGE JOIN OUTER               |               |      1 |      1 |    45 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|  10 |           NESTED LOOPS OUTER            |               |      1 |      1 |    20 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|  11 |            NESTED LOOPS OUTER           |               |      1 |      1 |     7 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  12 |             FAST DUAL                   |               |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|  13 |             VIEW                        |               |      1 |      1 |     7 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 14 |              TABLE ACCESS BY INDEX ROWID| JOBS          |      1 |      1 |    27 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  15 |               INDEX FULL SCAN           | JOB_ID_PK     |      1 |     19 |       |     1   (0)| 00:00:01 |     19 |00:00:00.01 |       1 |
|  16 |            TABLE ACCESS BY INDEX ROWID  | JOB_HISTORY   |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 17 |             INDEX RANGE SCAN            | JHIST_JOB_IX  |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|  18 |           BUFFER SORT                   |               |      1 |      1 |    25 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  19 |            VIEW                         |               |      1 |      1 |    25 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  20 |             TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |      1 |      1 |    30 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 21 |              INDEX RANGE SCAN           | EMP_NAME_IX   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  22 |         TABLE ACCESS BY INDEX ROWID     | EMPLOYEES     |      1 |      6 |   144 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 23 |          INDEX RANGE SCAN               | EMP_JOB_IX    |      1 |      6 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|  24 |       TABLE ACCESS BY INDEX ROWID       | EMPLOYEES     |      1 |      1 |    19 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 25 |        INDEX UNIQUE SCAN                | EMP_EMP_ID_PK |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  26 |      TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 27 |       INDEX UNIQUE SCAN                 | DEPT_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 28 |     INDEX UNIQUE SCAN                   | LOC_ID_PK     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  29 |    TABLE ACCESS BY INDEX ROWID          | LOCATIONS     |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------------------------

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

  14 - filter("J"."JOB_TITLE"='President')
  17 - access("JH"."JOB_ID"="J"."JOB_ID")
  21 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  23 - access("E2"."JOB_ID"="J"."JOB_ID")
       filter("E2".ROWID<>"E"."ROWID")
  25 - access("E3"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
       filter(("E3".ROWID<>"E2"."ROWID" AND "E3".ROWID<>"E"."ROWID"))
  27 - access("D"."DEPARTMENT_ID"=COALESCE("from$_subquery$_009"."QCSJ_C000000000700003","from$_subquery$_009"."DEPARTMENT_ID","E3"."DEPART
              MENT_ID"))
  28 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

My previous attempts were here and here.

SQL Mini-challenge entry: Craig Martin v2

by Craig Martin

My original entry is here. In the comments, Kim Berg Hansen made a great point that there was a flaw in my logic where if there were no President, the correct results wouldn’t be returned. This is an excellent catch, and a perfect example of why all code should be peer reviewed! While looking back through the code to fix this, I found a way to get the buffer count down to 14 as well.

My code now looks like:

select /*+ ordered use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */  distinct l.location_id, l.city
from dual
    left outer join jobs j
        on j.job_title = 'President'
    left outer join job_history jh
        on jh.job_id = j.job_id
    left outer join employees e
        on (e.first_name = 'Steven' and e.last_name = 'King')
    left outer join employees e2
        on e2.job_id = j.job_id
        and e2.rowid <> e.rowid
    left outer join employees e3
        on e3.employee_id = jh.employee_id
        and e3.rowid <> e.rowid
        and e3.rowid <> e2.rowid
    inner join departments d
        on d.department_id = coalesce(e.department_id, e2.department_id, e3.department_id)
    inner join locations l
        on d.location_id = l.location_id;

This now results in 14 buffers, as I no longer hit the employee table the 2nd time (because President and Stephen King are the same person).

PLAN_TABLE_OUTPUT
SQL_ID  8h2sbxvfppfzz, child number 0
-------------------------------------
select /*+ ordered use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d)
use_nl(l) */  distinct l.location_id, l.city from dual     left outer
join jobs j         on j.job_title = 'President'     left outer join
job_history jh         on jh.job_id = j.job_id     left outer join
employees e         on (e.first_name = 'Steven' and e.last_name =
'King')     left outer join employees e2         on e2.job_id =
j.job_id         and e2.rowid <> e.rowid     left outer join employees
e3         on e3.employee_id = jh.employee_id         and e3.rowid <>
e.rowid         and e3.rowid <> e2.rowid     inner join departments d
      on d.department_id = coalesce(e.department_id, e2.department_id,
e3.department_id)     inner join locations l         on d.location_id =
l.location_id

Plan hash value: 1707092081

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |       |    28 (100)|          |      1 |00:00:00.01 |      14 |
|   1 |  HASH UNIQUE                           |               |      1 |      6 |   552 |    28   (4)| 00:00:01 |      1 |00:00:00.01 |      14 |
|   2 |   NESTED LOOPS                         |               |      1 |        |       |            |          |      1 |00:00:00.01 |      14 |
|   3 |    NESTED LOOPS                        |               |      1 |      6 |   552 |    27   (0)| 00:00:01 |      1 |00:00:00.01 |      13 |
|   4 |     NESTED LOOPS                       |               |      1 |      6 |   480 |    21   (0)| 00:00:01 |      1 |00:00:00.01 |      12 |
|   5 |      NESTED LOOPS OUTER                |               |      1 |      6 |   438 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   6 |       VIEW                             |               |      1 |      6 |   324 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   7 |        NESTED LOOPS OUTER              |               |      1 |      6 |   414 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   8 |         VIEW                           |               |      1 |      1 |    45 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|   9 |          MERGE JOIN OUTER              |               |      1 |      1 |    45 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|  10 |           NESTED LOOPS OUTER           |               |      1 |      1 |    20 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|  11 |            NESTED LOOPS OUTER          |               |      1 |      1 |     7 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|  12 |             FAST DUAL                  |               |      1 |      1 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |
|  13 |             VIEW                       |               |      1 |      1 |     7 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|* 14 |              TABLE ACCESS FULL         | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|  15 |            TABLE ACCESS BY INDEX ROWID | JOB_HISTORY   |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 16 |             INDEX RANGE SCAN           | JHIST_JOB_IX  |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|  17 |           BUFFER SORT                  |               |      1 |      1 |    25 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  18 |            VIEW                        |               |      1 |      1 |    25 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  19 |             TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      1 |      1 |    30 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 20 |              INDEX RANGE SCAN          | EMP_NAME_IX   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  21 |         TABLE ACCESS BY INDEX ROWID    | EMPLOYEES     |      1 |      6 |   144 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 22 |          INDEX RANGE SCAN              | EMP_JOB_IX    |      1 |      6 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|  23 |       TABLE ACCESS BY INDEX ROWID      | EMPLOYEES     |      1 |      1 |    19 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 24 |        INDEX UNIQUE SCAN               | EMP_EMP_ID_PK |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  25 |      TABLE ACCESS BY INDEX ROWID       | DEPARTMENTS   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 26 |       INDEX UNIQUE SCAN                | DEPT_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 27 |     INDEX UNIQUE SCAN                  | LOC_ID_PK     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  28 |    TABLE ACCESS BY INDEX ROWID         | LOCATIONS     |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------------------------------------

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

  14 - filter("J"."JOB_TITLE"='President')
  16 - access("JH"."JOB_ID"="J"."JOB_ID")
  20 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  22 - access("E2"."JOB_ID"="J"."JOB_ID")
       filter("E2".ROWID<>"E"."ROWID")
  24 - access("E3"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
       filter(("E3".ROWID<>"E2"."ROWID" AND "E3".ROWID<>"E"."ROWID"))
  26 - access("D"."DEPARTMENT_ID"=COALESCE("from$_subquery$_009"."QCSJ_C000000000700003","from$_subquery$_009"."DEPARTMENT_ID","E3"."DEPAR
              TMENT_ID"))
  27 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

SQL Mini-challenge entry: Craig Martin

by Craig Martin

Like always, the August 2014 issue of the NoCOUG Journal is filled with excellent content.  One of the features is a SQL Mini-challenge.  This is my entry:

select /*+ ordered use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */
    distinct l.location_id, l.city
from
    jobs j
    left outer join job_history jh
        on jh.job_id = j.job_id
    left outer join employees e
        on (e.first_name = 'Steven' and e.last_name = 'King')
    left outer join employees e2
        on e2.job_id = j.job_id
    left outer join employees e3
        on e3.employee_id = jh.employee_id
    inner join departments d
        on d.department_id = coalesce(e.department_id, e2.department_id, e3.department_id)
    inner join locations l
        on d.location_id = l.location_id
where
    j.job_title = 'President';

Looking at the execution plan, it comes out to 15 Buffers, which isn’t too bad:

PLAN_TABLE_OUTPUT
SQL_ID  1dgjudpz90amq, child number 0
-------------------------------------
select /*+ ordered use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d)
use_nl(l) */  distinct l.location_id, l.city from jobs j     left outer
join job_history jh         on jh.job_id = j.job_id     left outer join
employees e         on (e.first_name = 'Steven' and e.last_name =
'King')     left outer join employees e2         on e2.job_id =
j.job_id     left outer join employees e3         on e3.employee_id =
jh.employee_id     inner join departments d         on d.department_id
= coalesce(e.department_id, e2.department_id, e3.department_id)
inner join locations l         on d.location_id = l.location_id where
  j.job_title = 'President'

Plan hash value: 3165520182

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |       |    29 (100)|          |      1 |00:00:00.01 |      15 |
|   1 |  HASH UNIQUE                         |               |      1 |      7 |   637 |    29   (4)| 00:00:01 |      1 |00:00:00.01 |      15 |
|   2 |   NESTED LOOPS                       |               |      1 |        |       |            |          |      1 |00:00:00.01 |      15 |
|   3 |    NESTED LOOPS                      |               |      1 |      7 |   637 |    28   (0)| 00:00:01 |      1 |00:00:00.01 |      14 |
|   4 |     NESTED LOOPS                     |               |      1 |      7 |   553 |    21   (0)| 00:00:01 |      1 |00:00:00.01 |      13 |
|   5 |      NESTED LOOPS OUTER              |               |      1 |      7 |   504 |    14   (0)| 00:00:01 |      1 |00:00:00.01 |      11 |
|   6 |       NESTED LOOPS OUTER             |               |      1 |      7 |   455 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |      11 |
|   7 |        MERGE JOIN OUTER              |               |      1 |      1 |    53 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|   8 |         NESTED LOOPS OUTER           |               |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|*  9 |          TABLE ACCESS FULL           | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|  10 |          TABLE ACCESS BY INDEX ROWID | JOB_HISTORY   |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 11 |           INDEX RANGE SCAN           | JHIST_JOB_IX  |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|  12 |         BUFFER SORT                  |               |      1 |      1 |    13 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  13 |          VIEW                        |               |      1 |      1 |    13 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|  14 |           TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 15 |            INDEX RANGE SCAN          | EMP_NAME_IX   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  16 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEES     |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 17 |         INDEX RANGE SCAN             | EMP_JOB_IX    |      1 |      6 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  18 |       TABLE ACCESS BY INDEX ROWID    | EMPLOYEES     |      1 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 19 |        INDEX UNIQUE SCAN             | EMP_EMP_ID_PK |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|  20 |      TABLE ACCESS BY INDEX ROWID     | DEPARTMENTS   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 21 |       INDEX UNIQUE SCAN              | DEPT_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 22 |     INDEX UNIQUE SCAN                | LOC_ID_PK     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  23 |    TABLE ACCESS BY INDEX ROWID       | LOCATIONS     |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   9 - filter("J"."JOB_TITLE"='President')
  11 - access("JH"."JOB_ID"="J"."JOB_ID")
  15 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  17 - access("E2"."JOB_ID"="J"."JOB_ID")
  19 - access("E3"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
  21 - access("D"."DEPARTMENT_ID"=COALESCE("E"."DEPARTMENT_ID","E2"."DEPARTMENT_ID","E3"."DEPARTMENT_ID"))
  22 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

I get this buffer count by doing a select for each of the different filter criteria and doing the equivalent of a cartesian join of those records, since I have the knowledge that each produces either 1 or 0 records, so will be more efficient than access by the joins to other tables. Once I have all the possible records, I join via nested loops to departments and finally to locations, so I only hit each of those tables via index once as well.

I look forward to the other responses, and seeing if anyone can get a lower buffer count. Good luck!

Craig