Northern California Oracle Users Group

Home » Uncategorized » SQL Mini-challenge entry: Craig Martin v4

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
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: