Northern California Oracle Users Group

Home » Articles posted by emmanuelhumblot

Author Archives: emmanuelhumblot

SQL Mini-challenge entry: Emmanuel Humblot

by Emmanuel Humblot

Hello,

This is my contribution. The number of Buffer Gets I reach is 12.

This is achieved by minimizing the number of query blocks (2) while avoiding outer joins.

Tables here are very small so TABLE ACCESS FULL is not a problem and is even often preferable.

Regards,

Emmanuel Humblot

 

 

SET LINES 300
SET PAGES 50

SELECT
/*+ GATHER_PLAN_STATISTICS */
L.LOCATION_ID
,L.CITY
FROM
EMPLOYEES E
,JOBS J
,LOCATIONS L
,DEPARTMENTS D
WHERE
J.JOB_ID = E.JOB_ID
AND
( (E.FIRST_NAME = 'Steven'  AND  E.LAST_NAME = 'King')
OR
J.JOB_TITLE = 'President')
AND
D.LOCATION_ID = L.LOCATION_ID
AND
E.DEPARTMENT_ID = D.DEPARTMENT_ID
UNION
SELECT /*+ GATHER_PLAN_STATISTICS */
L.LOCATION_ID
,L.CITY
FROM
EMPLOYEES E
,JOBS J
,JOB_HISTORY JH
,LOCATIONS L
,DEPARTMENTS D
WHERE
JH.EMPLOYEE_ID = E.EMPLOYEE_ID
AND
JH.JOB_ID = J.JOB_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND J.JOB_TITLE = 'President';

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL IOSTATS LAST'));


LOCATION_ID CITY
----------- ------------------------------
1700        Seattle

1 row selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------

SQL_ID du03stsu10th9, child number 0

-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */
l.location_id, l.city FROM employees e,
jobs j,locations l,departments d WHERE j.job_id = e.job_id AND (
(e.first_name = 'Steven' AND e.last_name = 'King') OR j.job_title =
'President' ) and d.location_id = l.location_id AND e.department_id =
d.department_id union SELECT /*+ GATHER_PLAN_STATISTICS */
l.location_id, l.city FROM employees
e,jobs j,job_history jh,locations l,departments d WHERE
jh.employee_id=e.employee_id AND jh.job_id = j.job_id and
d.location_id = l.location_id AND e.department_id = d.department_id AND
j.job_title = 'President'

Plan hash value: 623879073

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)|  E-Time  | A-Rows |   A-Time   | Buffers  |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT                    |                |     1 |        |       |    18 (100)|          |      1 |00:00:00.01 |       12 |
| 1  |  SORT UNIQUE                        |                |     1 |      7 |   528 |     18 (56)| 00:00:01 |      1 |00:00:00.01 |       12 |
| 2  |   UNION-ALL                         |                |     1 |        |       |            |          |      1 |00:00:00.01 |       12 |
|* 3 |    HASH JOIN                        |                |     1 |      6 |   462 |     10 (20)| 00:00:01 |      1 |00:00:00.01 |        9 |
|* 4 |     HASH JOIN                       |                |     1 |      6 |   390 |      7 (15)| 00:00:01 |      1 |00:00:00.01 |        7 |
|* 5 |      HASH JOIN                                       |     1 |      6 |   348 |      5 (20)| 00:00:01 |      1 |00:00:00.01 |        5 |
| 6  |       TABLE ACCESS FULL             | JOBS           |     1 |     19 |   513 |       2 (0)| 00:00:01 |     19 |00:00:00.01 |        2 |
| 7  |       TABLE ACCESS FULL             | EMPLOYEES      |     1 |    107 |  3317 |       2 (0)| 00:00:01 |    107 |00:00:00.01 |        3 |
| 8  |      TABLE ACCESS FULL              | DEPARTMENTS    |     1 |     27 |   189 |       2 (0)| 00:00:01 |     27 |00:00:00.01 |        2 |
| 9  |     TABLE ACCESS FULL               | LOCATIONS      |     1 |     23 |   276 |       2 (0)| 00:00:01 |     23 |00:00:00.01 |        2 |
| 10 |    NESTED LOOPS                     |                |     1 |        |       |            |          |      0 |00:00:00.01 |        3 |
| 11 |     NESTED LOOPS                    |                |     1 |      1 |    66 |       6 (0)| 00:00:01 |      0 |00:00:00.01 |        3 |
| 12 |      NESTED LOOPS                   |                |     1 |      1 |    54 |       5 (0)| 00:00:01 |      0 |00:00:00.01 |        3 |
| 13 |       NESTED LOOPS                  |                |     1 |      1 |    47 |       4 (0)| 00:00:01 |      0 |00:00:00.01 |        3 |
| 14 |        NESTED LOOPS                 |                |     1 |      1 |    40 |       3 (0)| 00:00:01 |      0 |00:00:00.01 |        3 |
|* 15|         TABLE ACCESS FULL           | JOBS           |     1 |      1 |    27 |       2 (0)| 00:00:01 |      1 |00:00:00.01 |        2 |
| 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 |         TABLE ACCESS BY INDEX ROWID | EMPLOYEES      |     0 |      1 |     7 |       1 (0)| 00:00:01 |      0 |00:00:00.01 |        0 |
|* 19|          INDEX UNIQUE SCAN          | EMP_EMP_ID_PKX |     0 |      1 |       |       0 (0)|          |      0 |00:00:00.01 |        0 |
| 20 |         TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |     0 |      1 |     7 |       1 (0)| 00:00:01 |      0 |00:00:00.01 |        0 |
|* 21|          INDEX UNIQUE SCAN          | DEPT_ID_PKX    |     0 |      1 |       |       0 (0)|          |      0 |00:00:00.01 |        0 |
|* 22|         INDEX UNIQUE SCAN           | LOC_ID_PKX     |     0 |      1 |       |       0 (0)|          |      0 |00:00:00.01 |        0 |
| 23 |        TABLE ACCESS BY INDEX ROWID  | LOCATIONS      |     0 |      1 |    12 |       1 (0)| 00:00:01 |      0 |00:00:00.01 |        0 |
-----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------
3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - access("J"."JOB_ID"="E"."JOB_ID")
filter((("E"."FIRST_NAME"='Steven' AND "E"."LAST_NAME"='King') OR "J"."JOB_TITLE"='President'))
15 - filter("J"."JOB_TITLE"='President')
17 - access("JH"."JOB_ID"="J"."JOB_ID")
19 - access("JH"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
21 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
22 - access("D"."LOCATION_ID"="L"."LOCATION_ID")