Northern California Oracle Users Group

Home » Articles posted by jimmybrock

Author Archives: jimmybrock

SQL Mini-challenge entry: CTE v2

by Jimmy Brock

Modified data set “curr_job” to return anyone that currently has the job_title of ‘President’ – even if his or her name is not Steven King.

Modify data set “job_hist” to return any employee that has ever had the job_id ‘AD_PRES’.

Buffer gets are now 9.

jbrock@ORA1> set serveroutput off
jbrock@ORA1> alter session set statistics_level=all ;

jbrock@ORA1> col version for a20
jbrock@ORA1> select distinct version from product_component_version;

VERSION
--------------------
11.2.0.1.0

WITH    emps
AS
(
      SELECT   location_id
             , city
             , employee_id
             , job_id
      FROM
          ( SELECT
                     l.location_id
                   , l.city
                   , e.employee_id
                   , e.job_id
                   -- we only want one instance of 'Steven' 'King'
                   , ROW_NUMBER() OVER (ORDER BY employee_id) RN
            FROM
                     hr.employees   e
                   , hr.departments d
                   , hr.locations   l
            WHERE
                     d.department_id = e.department_id
            AND
                     l.location_id   = d.location_id
            AND
                     e.first_name    = 'Steven'
            AND
                     e.last_name     = 'King'
          )
      WHERE
            rn = 1
 )
 ,      curr_job
 AS
 (
     SELECT    /*+ index_ffs(j job_id_pk) */
               e.location_id
             , e.city
             , e.employee_id
             , j.job_title
     FROM
               emps e
             , hr.jobs   j
     WHERE
               j.job_id = e.job_id (+)
     AND
               j.job_title = 'President'
)
,
       job_hist
AS
(
     SELECT   j.location_id
            , j.city
     FROM
              curr_job        j
            , hr.job_history  jh
     WHERE
              j.employee_id = jh.employee_id (+)
     AND
            (  j.job_title = 'President'
        OR     jh.job_id = 'AD_PRES'
            )
)
SELECT * FROM job_hist ;

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

-- -----------------------------------------------------------------------------
-- EXECUTION PLAN
-- -----------------------------------------------------------------------------
jbrock@ORA1> select * from table(dbms_xplan.display_cursor(null,null,'last iostats'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  7m5nbk4dw4yjx, child number 1
-------------------------------------
WITH    emps AS (       SELECT   location_id              , city
      , employee_id              , job_id       FROM           ( SELECT
                     l.location_id                    , l.city
          , e.employee_id                    , e.job_id
   -- we only want one instance of 'Steven' 'King'                    ,
ROW_NUMBER() OVER (ORDER BY employee_id) RN             FROM
          hr.employees   e                    , hr.departments d
            , hr.locations   l             WHERE
d.department_id = e.department_id             AND
l.location_id   = d.location_id             AND
e.first_name    = 'Steven'             AND
e.last_name     = 'King'           )       WHERE             rn = 1  )
,      curr_job  AS  (      SELECT   /*+ index_ffs(j job_id_pk) */
         e.location_id              , e.city              ,
e.employee_id

Plan hash value: 871535527

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS OUTER                |                   |      1 |      1 |      1 |00:00:00.01 |       9 |
|*  2 |   HASH JOIN OUTER                  |                   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS BY INDEX ROWID     | JOBS              |      1 |      1 |      1 |00:00:00.01 |       2 |
|   4 |     INDEX FULL SCAN                | JOB_ID_PK         |      1 |     19 |     19 |00:00:00.01 |       1 |
|*  5 |    VIEW                            |                   |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  6 |     WINDOW SORT PUSHED RANK        |                   |      1 |      1 |      1 |00:00:00.01 |       6 |
|   7 |      NESTED LOOPS                  |                   |      1 |        |      1 |00:00:00.01 |       6 |
|   8 |       NESTED LOOPS                 |                   |      1 |      1 |      1 |00:00:00.01 |       5 |
|   9 |        NESTED LOOPS                |                   |      1 |      1 |      1 |00:00:00.01 |       4 |
|  10 |         TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |      1 |      1 |      1 |00:00:00.01 |       2 |
|* 11 |          INDEX RANGE SCAN          | EMP_NAME_IX       |      1 |      1 |      1 |00:00:00.01 |       1 |
|  12 |         TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |      1 |      1 |00:00:00.01 |       2 |
|* 13 |          INDEX UNIQUE SCAN         | DEPT_ID_PK        |      1 |      1 |      1 |00:00:00.01 |       1 |
|* 14 |        INDEX UNIQUE SCAN           | LOC_ID_PK         |      1 |      1 |      1 |00:00:00.01 |       1 |
|  15 |       TABLE ACCESS BY INDEX ROWID  | LOCATIONS         |      1 |      1 |      1 |00:00:00.01 |       1 |
|* 16 |   INDEX RANGE SCAN                 | JHIST_EMPLOYEE_IX |      1 |      1 |      0 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("J"."JOB_ID"="JOB_ID")
   3 - filter("J"."JOB_TITLE"='President')
   5 - filter("RN"=1)
   6 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPLOYEE_ID")<=1)
  11 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  13 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  14 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
  16 - access("EMPLOYEE_ID"="JH"."EMPLOYEE_ID")

Note
-----
   - cardinality feedback used for this statement

58 rows selected.
Advertisements

SQL Mini-challenge entry: CTE

by Jimmy Brock

I decided to toss my hat into the ring. Lots of creative solutions, thus far.

I’m using common table expressions, this way I only have to visit the data blocks once.

Got it down to 9 buffer gets.

jbrock@ORA1> set serveroutput off
jbrock@ORA1> alter session set statistics_level=all ;

jbrock@ORA1> col version for a20
jbrock@ORA1> select distinct version from product_component_version;

VERSION
--------------------
11.2.0.1.0

WITH    emps
AS
(
      SELECT   location_id
             , city
             , employee_id
             , job_id
      FROM
          ( SELECT
                     l.location_id
                   , l.city
                   , e.employee_id
                   , e.job_id
                     -- we only want one instance of 'Steven' 'King'
                   , ROW_NUMBER() OVER (ORDER BY employee_id) RN
            FROM
                     hr.employees   e
                   , hr.departments d
                   , hr.locations   l
            WHERE
                     d.department_id = e.department_id
            AND
                     l.location_id   = d.location_id
            AND
                     e.first_name    = 'Steven'
            AND
                     e.last_name     = 'King'
          )
      WHERE
            rn = 1
 )
 ,      curr_job
 AS
 (
     SELECT
               e.location_id
             , e.city
             , e.employee_id
             , j.job_title
     FROM
               emps e
             , hr.jobs   j
     WHERE
               j.job_id = e.job_id
     AND
               j.job_title = 'President'
)
,
       job_hist
AS
(
     SELECT   j.location_id
            , j.city
     FROM
              curr_job        j
            , hr.job_history  jh
     WHERE
              j.employee_id = jh.employee_id (+)
     AND
              j.job_title = 'President'
)
SELECT * FROM job_hist ;

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

-- -----------------------------------------------------------------------------
-- EXECUTION PLAN
-- -----------------------------------------------------------------------------
jbrock@ORA1> select * from table(dbms_xplan.display_cursor(null,null,'last iostats'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  6u711cztagqbz, child number 0
-------------------------------------
WITH    emps AS (       SELECT   location_id              , city
      , employee_id              , job_id       FROM           ( SELECT
                     l.location_id                    , l.city
          , e.employee_id                    , e.job_id
   , ROW_NUMBER() OVER (ORDER BY employee_id) RN             FROM
               hr.employees   e                    , hr.departments d
                 , hr.locations   l             WHERE
   d.department_id = e.department_id             AND
  l.location_id   = d.location_id             AND
e.first_name    = 'Steven'             AND
e.last_name     = 'King'           )       WHERE             rn = 1  )
,      curr_job  AS  (      SELECT                e.location_id
     , e.city              , e.employee_id              , j.job_title
   FROM                emps e              , hr.jobs   j      WHERE

Plan hash value: 3854800225

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                       |                   |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS                      |                   |      1 |      1 |      1 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS OUTER               |                   |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  4 |     VIEW                            |                   |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  5 |      WINDOW SORT PUSHED RANK        |                   |      1 |      1 |      1 |00:00:00.01 |       6 |
|   6 |       NESTED LOOPS                  |                   |      1 |        |      1 |00:00:00.01 |       6 |
|   7 |        NESTED LOOPS                 |                   |      1 |      1 |      1 |00:00:00.01 |       5 |
|   8 |         NESTED LOOPS                |                   |      1 |      1 |      1 |00:00:00.01 |       4 |
|   9 |          TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |      1 |      1 |      1 |00:00:00.01 |       2 |
|* 10 |           INDEX RANGE SCAN          | EMP_NAME_IX       |      1 |      1 |      1 |00:00:00.01 |       1 |
|  11 |          TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |      1 |      1 |00:00:00.01 |       2 |
|* 12 |           INDEX UNIQUE SCAN         | DEPT_ID_PK        |      1 |      1 |      1 |00:00:00.01 |       1 |
|* 13 |         INDEX UNIQUE SCAN           | LOC_ID_PK         |      1 |      1 |      1 |00:00:00.01 |       1 |
|  14 |        TABLE ACCESS BY INDEX ROWID  | LOCATIONS         |      1 |      1 |      1 |00:00:00.01 |       1 |
|* 15 |     INDEX RANGE SCAN                | JHIST_EMPLOYEE_IX |      1 |      1 |      0 |00:00:00.01 |       1 |
|* 16 |    INDEX UNIQUE SCAN                | JOB_ID_PK         |      1 |      1 |      1 |00:00:00.01 |       1 |
|* 17 |   TABLE ACCESS BY INDEX ROWID       | JOBS              |      1 |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("RN"=1)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPLOYEE_ID")<=1)
  10 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  12 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  13 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
  15 - access("EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
  16 - access("J"."JOB_ID"="JOB_ID")
  17 - filter("J"."JOB_TITLE"='President')

54 rows selected.