Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge entry: Emmanuel Humblot

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")

Advertisements

3 Comments

  1. Nice, Emmanuel.

    At first I was going to object slightly, since your code assumes that every employee has a job, and I thought it would be wrong if there was an employee named Steven King without a job. But then I looked at the schema and see that employees.job_id is not null and has foreign key to jobs, so that is OK. Good lesson here that one should look at the tables and constraints and use that knowledge for better code 😉

    Even so I cannot reproduce your 12 buffers on my 12.1.0.1.0 instance? I have gathered compute statistics on the HR schema, but running your code never gets below 21 buffers for me – it insists on indexes and nested loops. I’ll try if I can hint it to get it to show same results as you.

    Like

  2. […] Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to […]

    Like

  3. Hello Kim Berg,

    Thank you for your feedback. Have you checked that your parameters optimizer_index_caching and optimizer_index_cost_adj are set to the default value ? If not it can affect the behaviour of the optimizer, making it more or less prone to using indexes than by default.

    To test a query with proper values, you don’t have to change the parameter values neither at the instance nor at the session level.

    All you have to do is to include the following hints in the query, which results in resetting the parameters at the default value just for the execution of the query :

    OPT_PARAM(‘OPTIMIZER_INDEX_CACHING’,0)
    OPT_PARAM(‘OPTIMIZER_INDEX_COST_ADJ’,100)

    Best regards,

    Emmanuel

    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: