Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge entry: CTE v2

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

2 Comments

  1. Sorry, Jimmy, but I still think it’s a bit flawed 😉

    Suppose in the data there is an employee named John Smith who has a job title of President (besides the Steven King who is also President.)

    Query curr_jobs will find the job title President and then outer join to “emps” which only contains the employees named Steven King. President John Smith will not be found.

    Like

    • jimmybrock says:

      Yes, I see that now. I fixed it locally but the buffers went up – duh. I’m working on a different solution now. Hope to post it this weekend.

      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: