Northern California Oracle Users Group

Home » Articles posted by Rafu

Author Archives: Rafu

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/