Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge entry: Timo Raitalaakso

SQL Mini-challenge entry: Timo Raitalaakso

by Timo Raitalaakso

Here is my approach to the problem. I will be using the “Anything not explicitly disallowed by the problem statement is also permissible.” approach.

One key to query performance is the correct organization of the rows according to the access path. One way to get performance is to use indexing.

create index ej_idx on hr.employees(job_id,department_id) compress 2;
create index e_idx on hr.employees(first_name,last_name,department_id);
create unique index ejh_idx on hr.employees(employee_id,department_id);
create unique index l_idx on hr.locations(location_id,city);
create unique index d_idx on hr.departments(department_id,location_id);
create unique index j_idx on hr.jobs(job_title,job_id);
create index jh_idx on hr.job_history(job_id,employee_id);

The rewriten query from my indexes.

SELECT /*+qb_name(qd) ordered use_nl(d) index(d d_idx) gather_plan_statistics*/
      d.location_id
    , ( SELECT /*+qb_name(ql)*/
                   l.city
              FROM locations l
             WHERE d.location_id = l.location_id ) city
  FROM ( SELECT /*+qb_name(qe)*/
                e.department_id
           FROM employees e
          WHERE e.first_name = 'Steven'
            AND e.last_name = 'King'
          UNION
         SELECT /*+qb_name(qej) leading(j ej)*/
                ej.department_id
           FROM employees ej
              , jobs j
          WHERE j.job_id = ej.job_id
            AND j.job_title = 'President'
          UNION
         SELECT /*+qb_name(qejh) leading(j2 jh ejh)*/
                ejh.department_id
           FROM employees ejh
              , job_history jh
              , jobs j2
          WHERE jh.employee_id = ejh.employee_id
            AND j2.job_id = jh.job_id
            AND j2.job_title = 'President'
      ) ee
      , departments d
  where ee.department_id = d.department_id
;

select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));

My query plan has 7 Buffers reported

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |       |    17 (100)|          |      1 |00:00:00.01 |       7 |
|*  1 |  INDEX RANGE SCAN      | L_IDX   |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   2 |  NESTED LOOPS          |         |      1 |      8 |   160 |    17  (18)| 00:00:01 |      1 |00:00:00.01 |       7 |
|   3 |   VIEW                 |         |      1 |      8 |   104 |     9  (34)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   4 |    SORT UNIQUE         |         |      1 |      8 |   299 |     9  (89)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   5 |     UNION-ALL          |         |      1 |        |       |            |          |      2 |00:00:00.01 |       5 |
|*  6 |      INDEX RANGE SCAN  | E_IDX   |      1 |      1 |    18 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   7 |      NESTED LOOPS      |         |      1 |      6 |   234 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  8 |       INDEX RANGE SCAN | J_IDX   |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|*  9 |       INDEX RANGE SCAN | EJ_IDX  |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  10 |      NESTED LOOPS      |         |      1 |      1 |    47 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|  11 |       NESTED LOOPS     |         |      1 |      1 |    40 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|* 12 |        INDEX RANGE SCAN| J_IDX   |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|* 13 |        INDEX RANGE SCAN| JH_IDX  |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 14 |       INDEX RANGE SCAN | EJH_IDX |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 15 |   INDEX RANGE SCAN     | D_IDX   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------------------

It is possible to get rid of those Buffers all together.

alter session set result_cache_mode = force;

Also result_cache hint could be used. The first execution adds one buffer usage to the plan.

Plan hash value: 214272840

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            |      1 |        |       |    17 (100)|          |      1 |00:00:00.01 |       8 |
|*  1 |  INDEX RANGE SCAN       | L_IDX                      |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   2 |  RESULT CACHE           | 5s5u3km7j932sctf3w0d3f5bc6 |      1 |        |       |            |          |      1 |00:00:00.01 |       8 |
|   3 |   NESTED LOOPS          |                            |      1 |      8 |   160 |    17  (18)| 00:00:01 |      1 |00:00:00.01 |       7 |
|   4 |    VIEW                 |                            |      1 |      8 |   104 |     9  (34)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   5 |     SORT UNIQUE         |                            |      1 |      8 |   299 |     9  (89)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   6 |      UNION-ALL          |                            |      1 |        |       |            |          |      2 |00:00:00.01 |       5 |
|*  7 |       INDEX RANGE SCAN  | E_IDX                      |      1 |      1 |    18 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   8 |       NESTED LOOPS      |                            |      1 |      6 |   234 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  9 |        INDEX RANGE SCAN | J_IDX                      |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|* 10 |        INDEX RANGE SCAN | EJ_IDX                     |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  11 |       NESTED LOOPS      |                            |      1 |      1 |    47 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|  12 |        NESTED LOOPS     |                            |      1 |      1 |    40 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|* 13 |         INDEX RANGE SCAN| J_IDX                      |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|* 14 |         INDEX RANGE SCAN| JH_IDX                     |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|* 15 |        INDEX RANGE SCAN | EJH_IDX                    |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 16 |    INDEX RANGE SCAN     | D_IDX                      |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------------------------------------

But after that no Buffers reported as those are not used at all.

Plan hash value: 214272840

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            |      1 |        |       |    17 (100)|          |      1 |00:00:00.01 |
|*  1 |  INDEX RANGE SCAN       | L_IDX                      |      0 |      1 |    12 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|   2 |  RESULT CACHE           | 5s5u3km7j932sctf3w0d3f5bc6 |      1 |        |       |            |          |      1 |00:00:00.01 |
|   3 |   NESTED LOOPS          |                            |      0 |      8 |   160 |    17  (18)| 00:00:01 |      0 |00:00:00.01 |
|   4 |    VIEW                 |                            |      0 |      8 |   104 |     9  (34)| 00:00:01 |      0 |00:00:00.01 |
|   5 |     SORT UNIQUE         |                            |      0 |      8 |   299 |     9  (89)| 00:00:01 |      0 |00:00:00.01 |
|   6 |      UNION-ALL          |                            |      0 |        |       |            |          |      0 |00:00:00.01 |
|*  7 |       INDEX RANGE SCAN  | E_IDX                      |      0 |      1 |    18 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|   8 |       NESTED LOOPS      |                            |      0 |      6 |   234 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|*  9 |        INDEX RANGE SCAN | J_IDX                      |      0 |      1 |    27 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|* 10 |        INDEX RANGE SCAN | EJ_IDX                     |      0 |      6 |    72 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|  11 |       NESTED LOOPS      |                            |      0 |      1 |    47 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |
|  12 |        NESTED LOOPS     |                            |      0 |      1 |    40 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|* 13 |         INDEX RANGE SCAN| J_IDX                      |      0 |      1 |    27 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|* 14 |         INDEX RANGE SCAN| JH_IDX                     |      0 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|* 15 |        INDEX RANGE SCAN | EJH_IDX                    |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
|* 16 |    INDEX RANGE SCAN     | D_IDX                      |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------------------------------------

So my answer is seven or even zero.


Timo Raitalaakso http://rafudb.blogspot.fi/

Advertisements

4 Comments

  1. Rafu says:

    There is a bug in my answer here. The problem is the issue that comes when exists clauses are changed to joins. The results starts to have duplicates. Here is a correction to the problem.

    
    SELECT /*+qb_name(qd) ordered use_nl(d) index(d d_idx) gather_plan_statistics result_cache*/
          distinct d.location_id
        , ( SELECT /*+qb_name(ql)*/
                       l.city
                  FROM locations l
                 WHERE d.location_id = l.location_id ) city
      FROM ( SELECT /*+qb_name(qe)*/
                    e.department_id
               FROM employees e
              WHERE e.first_name = 'Steven'
                AND e.last_name = 'King'
              UNION
             SELECT /*+qb_name(qej) leading(j ej)*/
                    ej.department_id
               FROM employees ej
                  , jobs j
              WHERE j.job_id = ej.job_id
                AND j.job_title = 'President'
              UNION
             SELECT /*+qb_name(qejh) leading(j2 jh ejh)*/
                    ejh.department_id
               FROM employees ejh
                  , job_history jh
                  , jobs j2
              WHERE jh.employee_id = ejh.employee_id
                AND j2.job_id = jh.job_id
                AND j2.job_title = 'President'
          ) ee
          , departments d
      where ee.department_id = d.department_id
    ;
    
    select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));
    
    Plan hash value: 1265437879
    
    -------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |                            |      1 |        |       |    22 (100)|          |      1 |00:00:00.01 |       7 |
    |*  1 |  INDEX RANGE SCAN        | L_IDX                      |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    |   2 |  RESULT CACHE            | by74mp90p7h1ugfgwtcc4natpn |      1 |        |       |            |          |      1 |00:00:00.01 |       7 |
    |   3 |   HASH UNIQUE            |                            |      1 |      7 |   140 |    22  (19)| 00:00:01 |      1 |00:00:00.01 |       7 |
    |   4 |    NESTED LOOPS          |                            |      1 |      8 |   160 |    17  (18)| 00:00:01 |      1 |00:00:00.01 |       6 |
    |   5 |     VIEW                 |                            |      1 |      8 |   104 |     9  (34)| 00:00:01 |      1 |00:00:00.01 |       5 |
    |   6 |      SORT UNIQUE         |                            |      1 |      8 |   598 |     9  (34)| 00:00:01 |      1 |00:00:00.01 |       5 |
    |   7 |       UNION-ALL          |                            |      1 |        |       |            |          |      2 |00:00:00.01 |       5 |
    |*  8 |        INDEX RANGE SCAN  | E_IDX                      |      1 |      1 |    18 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    |   9 |        NESTED LOOPS      |                            |      1 |      6 |   234 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
    |* 10 |         INDEX RANGE SCAN | J_IDX                      |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    |* 11 |         INDEX RANGE SCAN | EJ_IDX                     |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    |  12 |        NESTED LOOPS      |                            |      1 |      1 |    47 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
    |  13 |         NESTED LOOPS     |                            |      1 |      1 |    40 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
    |* 14 |          INDEX RANGE SCAN| J_IDX                      |      1 |      1 |    27 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    |* 15 |          INDEX RANGE SCAN| JH_IDX                     |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
    |* 16 |         INDEX RANGE SCAN | EJH_IDX                    |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
    |* 17 |     INDEX RANGE SCAN     | D_IDX                      |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    -------------------------------------------------------------------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
    ---------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |                            |      1 |        |       |    14 (100)|          |      1 |00:00:00.01 |
    |*  1 |  INDEX RANGE SCAN        | L_IDX                      |      0 |      1 |    12 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |   2 |  RESULT CACHE            | by74mp90p7h1ugfgwtcc4natpn |      1 |        |       |            |          |      1 |00:00:00.01 |
    |   3 |   HASH UNIQUE            |                            |      0 |      1 |    20 |    14  (29)| 00:00:01 |      0 |00:00:00.01 |
    |   4 |    NESTED LOOPS          |                            |      0 |      1 |    20 |    12  (25)| 00:00:01 |      0 |00:00:00.01 |
    |   5 |     VIEW                 |                            |      0 |      3 |    39 |     9  (34)| 00:00:01 |      0 |00:00:00.01 |
    |   6 |      SORT UNIQUE         |                            |      0 |      3 |   208 |     9  (34)| 00:00:01 |      0 |00:00:00.01 |
    |   7 |       UNION-ALL          |                            |      0 |        |       |            |          |      0 |00:00:00.01 |
    |*  8 |        INDEX RANGE SCAN  | E_IDX                      |      0 |      1 |    18 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |   9 |        NESTED LOOPS      |                            |      0 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
    |* 10 |         INDEX RANGE SCAN | J_IDX                      |      0 |      1 |    27 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |* 11 |         INDEX RANGE SCAN | EJ_IDX                     |      0 |      1 |    12 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |  12 |        NESTED LOOPS      |                            |      0 |      1 |    47 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |
    |  13 |         NESTED LOOPS     |                            |      0 |      1 |    40 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
    |* 14 |          INDEX RANGE SCAN| J_IDX                      |      0 |      1 |    27 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |* 15 |          INDEX RANGE SCAN| JH_IDX                     |      0 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |* 16 |         INDEX RANGE SCAN | EJH_IDX                    |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    |* 17 |     INDEX RANGE SCAN     | D_IDX                      |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    ---------------------------------------------------------------------------------------------------------------------------------------
    
    

    Like

  2. Rafu says:

    Here is a materialized view approach. No query rewrite is needed as others are suggesting.

    create materialized view ej3 refresh complete on commit as (
      select ej.department_id
        from employees ej
           , jobs j
       WHERE j.job_id = ej.job_id
         AND j.job_title  = 'President'
    )
    ;
    
    create materialized view ejh4 refresh complete on commit as (
      select ejh.department_id
        from jobs j2
           , job_history jh
           , employees ejh
       where j2.job_id = jh.job_id
         and jh.employee_id = ejh.employee_id
         and j2.job_title = 'President'
    )
    ;
    
    create materialized view president_locations refresh complete on commit as (
      select l.location_id
           , l.city
       from  locations l,(
      select d.location_id from ej3, ejh4
           , employees e
           , departments d
       where e.department_id(+) = d.department_id
         and ej3.department_id(+) = d.department_id
         and ejh4.department_id(+) = d.department_id
         and e.first_name(+) = 'Steven'
         and e.last_name(+) = 'King'
         and (e.department_id is not null
            or ej3.department_id is not null
            or ejh4.department_id is not null)
      ) dd where dd.location_id = l.location_id
    )
    ;
    
    create index pl_idx on president_locations(location_id,city);
    
    select /*+gather_plan_statistics result_cache*/ distinct location_id,city 
      from president_locations
    ;
    
    select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));
    
    Plan hash value: 726281040
    
    --------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    --------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                            |      1 |        |       |     2 (100)|       |         1 |00:00:00.01 |       1 |
    |   1 |  RESULT CACHE       | 8fdqakukf1zgm5gtjsv1bbhvtw |      1 |        |       |            |       |         1 |00:00:00.01 |       1 |
    |   2 |   SORT UNIQUE NOSORT|                            |      1 |      1 |    11 |     2  (50)| 00:00:01 |      1 |00:00:00.01 |       1 |
    |   3 |    INDEX FULL SCAN  | PL_IDX                     |      1 |      1 |    11 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
    --------------------------------------------------------------------------------------------------------------------------------------------
    
    ----------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
    ----------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                            |      1 |        |       |     2 (100)|       |         1 |00:00:00.01 |
    |   1 |  RESULT CACHE       | 8fdqakukf1zgm5gtjsv1bbhvtw |      1 |        |       |            |       |         1 |00:00:00.01 |
    |   2 |   SORT UNIQUE NOSORT|                            |      0 |      1 |    11 |     2  (50)| 00:00:01 |      0 |00:00:00.01 |
    |   3 |    INDEX FULL SCAN  | PL_IDX                     |      0 |      1 |    11 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |
    ----------------------------------------------------------------------------------------------------------------------------------
    

    Like

    • True, query rewrite is not needed. Any of the solutions using materialized views can select directly from the mviews rather than use query rewrite. And as the challenge rules do not explicitly disallow this, that is probably OK 😉

      Using query rewrite might be a bit more “in the spirit” of the competion, as the queries would give the same answer even if the mviews were not there – the mviews “just” is a method to make the query use fewer buffer gets (like using fat indexes to avoid table access) and can (in a sense) be considered “just” a performance optimization to improve how the query gets its results.

      Even so in real life I would often choose to select from the mview directly also rather than query rewrite. If the mview would turn invalid, then only that bit of the application would fail. While using query rewrite would mean that if the mview fails, then those queries would suddenly use many many more buffer gets and choke the server 😉

      But what I dislike about this solution is the “refresh complete on commit”. I am actually a bit surprised it is allowed? 😉 I mean, every time DML happens on a single one of those 5 tables, then this mview needs a *complete* refresh when the transaction commits? Sure, it is not against the rules of the competition – but I wouldn’t want to see this in a production system without very very good reason. 😉

      Like

      • Iggy Fernandez says:

        Query rewrite is only available with Enterprise Edition. If one is using Standard Edition, then one must explicitly retrieve data directly from the materialized views.

        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: