Northern California Oracle Users Group

Home » Articles posted by Kim Berg Hansen

Author Archives: Kim Berg Hansen

SQL Mini-challenge entry: Kim Berg Hansen v6

by Kim Berg Hansen

Okay, okay, Iggy, you want one buffer get? I’ll do it πŸ˜‰

Actually two different versions that might be useful in different circumstances. First one goes like this:

create materialized view log on employees with rowid, commit scn
   (employee_id, job_id, first_name, last_name, department_id)
   including new values;
create materialized view log on departments with rowid, commit scn
   (department_id, location_id)
   including new values;
create materialized view log on locations with rowid, commit scn
   (location_id, city)
   including new values;
create materialized view log on jobs with rowid, commit scn
   (job_id, job_title)
   including new values;
create materialized view log on job_history with rowid, commit scn
   (job_id, employee_id)
   including new values;

create materialized view emp_jobs_mv
build immediate
refresh fast on commit
enable query rewrite
as
select e.first_name, e.last_name, j.job_title, l.location_id, l.city, 'HIST' record_type
     , j.rowid j_rowid, jh.rowid jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, job_history jh, employees e, departments d, locations l
 where jh.job_id = j.job_id
   and e.employee_id = jh.employee_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
union all
select e.first_name, e.last_name, j.job_title, l.location_id, l.city, 'CURR' record_type
     , j.rowid j_rowid, null jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, employees e, departments d, locations l
 where e.job_id = j.job_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id;

create index emp_jobs_kings_presidents on emp_jobs_mv (
   case
      when first_name = 'Steven' and last_name = 'King' then 1
      when job_title = 'President' then 1
      else 0
   end
 , location_id
 , city
);

begin
   dbms_stats.gather_schema_stats(
      'HR'
    , estimate_percent => null
    , cascade => true
   );
end;

We make a single fast refresh on commit mview that contains both names and job titles (current as well as historical.) And then to make it possible to just access a single index block we create a very specialized index with a “boolean” indicating whether the row is King or president.

Now we select the data where the “boolean” expression is “true”:

select /*+ gather_plan_statistics */
       distinct location_id, city
  from (
   select e.first_name, e.last_name, j.job_title, l.location_id, l.city
     from jobs j, job_history jh, employees e, departments d, locations l
    where jh.job_id = j.job_id
      and e.employee_id = jh.employee_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
   union all
   select e.first_name, e.last_name, j.job_title, l.location_id, l.city
     from jobs j, employees e, departments d, locations l
    where e.job_id = j.job_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
  )
 where case
          when first_name = 'Steven' and last_name = 'King' then 1
          when job_title = 'President' then 1
          else 0
       end = 1;

And we get the desired single buffer get:

-----------------------------------------------------------------------------------------------------------------------------------------
| 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 |  HASH UNIQUE      |                           |      1 |      1 |    19 |     2  (50)| 00:00:01 |      1 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN| EMP_JOBS_KINGS_PRESIDENTS |      1 |      1 |    19 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                         
Predicate Information (identified by operation id):                                                                                      
---------------------------------------------------                                                                                      
                                                                                                                                         
   2 - access("EMP_JOBS_MV"."SYS_NC00012$"=1)                                                                                            

That will scale and keep queries down to few buffer gets even for much more data – as long as there are few Kings and presidents πŸ˜‰

Alternative solution puts the predicates inside the mview, so the mview only contains the desired data and nothing more. The mview becomes less generally usable, but for specialized cases it might be OK (in a way it is similar to creating indexes on expressions that are NULL for non-desired data to make a very small index with only desired data.)

create materialized view kings_presidents_mv
build immediate
refresh fast on commit
enable query rewrite
as
select e.first_name, e.last_name, j.job_title, l.location_id, l.city, 'HIST' record_type
     , j.rowid j_rowid, jh.rowid jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, job_history jh, employees e, departments d, locations l
 where jh.job_id = j.job_id
   and e.employee_id = jh.employee_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
   and j.job_title = 'President'
union all
select e.first_name, e.last_name, j.job_title, l.location_id, l.city, 'CURR' record_type
     , j.rowid j_rowid, null jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, employees e, departments d, locations l
 where e.job_id = j.job_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
   and ( (e.first_name = 'Steven' and e.last_name = 'King')
         or j.job_title = 'President' );

alter table kings_presidents_mv modify
(
  first_name   not null,
  last_name    not null,
  job_title    not null,
  location_id  not null,
  city         not null,
  record_type  not null,
  j_rowid      not null,
  e_rowid      not null,
  d_rowid      not null,
  l_rowid      not null
);

create index kings_presidents_ix on kings_presidents_mv (
   location_id, city
);

begin
   dbms_stats.gather_schema_stats(
      'HR'
    , estimate_percent => null
    , cascade => true
   );
end;

And we can just select the entire mview index via query rewrite:

select /*+ gather_plan_statistics */
       distinct location_id, city
  from (
   select l.location_id, l.city
     from jobs j, job_history jh, employees e, departments d, locations l
    where jh.job_id = j.job_id
      and e.employee_id = jh.employee_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
      and j.job_title = 'President'
   union all
   select l.location_id, l.city
     from jobs j, employees e, departments d, locations l
    where e.job_id = j.job_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
      and ( (e.first_name = 'Steven' and e.last_name = 'King')
            or j.job_title = 'President' )
  );
------------------------------------------------------------------------------------------------------------------------------------
| 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 |  SORT UNIQUE NOSORT|                     |      1 |      1 |    11 |     2  (50)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN  | KINGS_PRESIDENTS_IX |      1 |      1 |    11 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------

No predicates are used at all – just return the contents of the index and done.

The setting of mview columns to NOT NULL to match the nullability of the master tables is one way to make that query use the index, if the columns had been nullable we would have got a full table scan of the mview instead, which would be 3 buffer gets on my 12.1 instance.

Alternative to setting the mview columns to NOT NULL could be to write the query like this:

select /*+ gather_plan_statistics */
       distinct location_id, city
  from (
   select l.location_id, l.city
     from jobs j, job_history jh, employees e, departments d, locations l
    where jh.job_id = j.job_id
      and e.employee_id = jh.employee_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
      and j.job_title = 'President'
   union all
   select l.location_id, l.city
     from jobs j, employees e, departments d, locations l
    where e.job_id = j.job_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
      and ( (e.first_name = 'Steven' and e.last_name = 'King')
            or j.job_title = 'President' )
  )
 where location_id is not null
   and city is not null;

Which also gets us index full scan:

------------------------------------------------------------------------------------------------------------------------------------
| 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 |  SORT UNIQUE NOSORT|                     |      1 |      1 |    11 |     2  (50)| 00:00:01 |      1 |00:00:00.01 |       1 |
|*  2 |   INDEX FULL SCAN  | KINGS_PRESIDENTS_IX |      1 |      1 |    11 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - filter(("KINGS_PRESIDENTS_MV"."LOCATION_ID" IS NOT NULL AND "KINGS_PRESIDENTS_MV"."CITY" IS NOT NULL))                       

But that requires evaluating an access predicate πŸ˜‰

SQL Mini-challenge entry: Kim Berg Hansen v5

by Kim Berg Hansen

Iggy Fernandez has been asking why not combine all my three materialized views into one to get a single buffer get.

Sure it is possible to create a single one that is fast refreshable on commit that can be used for query rewrite, as demonstrated by Vlado Barun. The trouble is we have predicates both on name and job title, so we need full index scan on the single mview (which can be done with one buffer only with small datasets), or we need two indexes on that mview anyway.

But my three mview solution can be reduced to two mviews in a way that can be useful even for larger datasets. We can combine the current job title and historical job title in one mview with a single index that can be used for predicates on job title.

create materialized view log on employees with rowid, commit scn
   (employee_id, job_id, first_name, last_name, department_id)
   including new values;
create materialized view log on departments with rowid, commit scn
   (department_id, location_id)
   including new values;
create materialized view log on locations with rowid, commit scn
   (location_id, city)
   including new values;
create materialized view log on jobs with rowid, commit scn
   (job_id, job_title)
   including new values;
create materialized view log on job_history with rowid, commit scn
   (job_id, employee_id)
   including new values;

create materialized view emp_name_location
build immediate
refresh fast on commit
enable query rewrite
as
select e.first_name, e.last_name, l.location_id, l.city
     , e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from employees e, departments d, locations l
 where d.department_id = e.department_id
   and l.location_id = d.location_id;

create materialized view job_title_location
build immediate
refresh fast on commit
enable query rewrite
as
select j.job_title, l.location_id, l.city, 'HIST' record_type
     , j.rowid j_rowid, jh.rowid jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, job_history jh, employees e, departments d, locations l
 where jh.job_id = j.job_id
   and e.employee_id = jh.employee_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
union all
select j.job_title, l.location_id, l.city, 'CURR' record_type
     , j.rowid j_rowid, null jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, employees e, departments d, locations l
 where e.job_id = j.job_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id;

create index emp_name_location_ix on emp_name_location (
   first_name, last_name, location_id, city
);

create index job_title_location_ix on job_title_location (
   job_title, location_id, city, record_type
);

Each of these mviews can work for query rewrite as demonstrated here:

select /*+ gather_plan_statistics */ location_id, city
  from (
   select j.job_title, l.location_id, l.city
     from jobs j, job_history jh, employees e, departments d, locations l
    where jh.job_id = j.job_id
      and e.employee_id = jh.employee_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
   union all
   select j.job_title, l.location_id, l.city
     from jobs j, employees e, departments d, locations l
    where e.job_id = j.job_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
  )
 where job_title = 'President';
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                       |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       1 |
|*  1 |  INDEX RANGE SCAN| JOB_TITLE_LOCATION_IX |      1 |      1 |    33 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("JOB_TITLE_LOCATION"."JOB_TITLE"='President')
select /*+ gather_plan_statistics */ l.location_id, l.city
  from employees e, departments d, 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';
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       1 |
|*  1 |  INDEX RANGE SCAN| EMP_NAME_LOCATION_IX |      1 |      1 |    31 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------------------------         

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

   1 - access("EMP_NAME_LOCATION"."FIRST_NAME"='Steven' AND "EMP_NAME_LOCATION"."LAST_NAME"='King')

So combining the two should do it, right?

select /*+ gather_plan_statistics */ location_id, city
  from (
   select j.job_title, l.location_id, l.city
     from jobs j, job_history jh, employees e, departments d, locations l
    where jh.job_id = j.job_id
      and e.employee_id = jh.employee_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
   union all
   select j.job_title, l.location_id, l.city
     from jobs j, employees e, departments d, locations l
    where e.job_id = j.job_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
  )
 where job_title = 'President'
 union /*distinct*/
select /*+ gather_plan_statistics */ l.location_id, l.city
  from employees e, departments d, 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';

Well, my 12.1 optimizer makes some weird decision in my opinion πŸ˜‰

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       8 |
|   1 |  SORT UNIQUE                    |                       |      1 |      2 |    91 |     4  (75)| 00:00:01 |      1 |00:00:00.01 |       8 |
|   2 |   UNION-ALL                     |                       |      1 |        |       |            |          |      2 |00:00:00.01 |       8 |
|*  3 |    INDEX RANGE SCAN             | JOB_TITLE_LOCATION_IX |      1 |      1 |    33 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   4 |    NESTED LOOPS SEMI            |                       |      1 |      1 |    58 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|*  5 |     MAT_VIEW REWRITE ACCESS FULL| JOB_TITLE_LOCATION    |      1 |    106 |  3286 |     3   (0)| 00:00:01 |    106 |00:00:00.01 |       4 |
|*  6 |     INDEX RANGE SCAN            | EMP_NAME_IX           |    106 |      1 |    27 |     0   (0)|          |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------------------------         

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

   3 - access("JOB_TITLE_LOCATION"."JOB_TITLE"='President')
   5 - filter("JOB_TITLE_LOCATION"."RECORD_TYPE"='CURR')
   6 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
       filter(ROWID="JOB_TITLE_LOCATION"."E_ROWID")

An extra inline view and the classic trick of “rownum >= 1” does the trick:

select /*+ gather_plan_statistics */ location_id, city
  from (
   select j.job_title, l.location_id, l.city
     from jobs j, job_history jh, employees e, departments d, locations l
    where jh.job_id = j.job_id
      and e.employee_id = jh.employee_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
   union all
   select j.job_title, l.location_id, l.city
     from jobs j, employees e, departments d, locations l
    where e.job_id = j.job_id
      and d.department_id = e.department_id
      and l.location_id = d.location_id
  )
 where job_title = 'President'
 union /*distinct*/
select *
  from (
   select /*+ gather_plan_statistics */ l.location_id, l.city
     from employees e, departments d, 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 rownum >= 1;

Now we get the 2 buffer gets we want:

----------------------------------------------------------------------------------------------------------------------------------------
| 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 |       2 |
|   1 |  SORT UNIQUE         |                       |      1 |      2 |    64 |     2  (50)| 00:00:01 |      1 |00:00:00.01 |       2 |
|   2 |   UNION-ALL          |                       |      1 |        |       |            |          |      2 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN  | JOB_TITLE_LOCATION_IX |      1 |      1 |    33 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|   4 |    COUNT             |                       |      1 |        |       |            |          |      1 |00:00:00.01 |       1 |
|*  5 |     FILTER           |                       |      1 |        |       |            |          |      1 |00:00:00.01 |       1 |
|*  6 |      INDEX RANGE SCAN| EMP_NAME_LOCATION_IX  |      1 |      1 |    31 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("JOB_TITLE_LOCATION"."JOB_TITLE"='President')
   5 - filter(ROWNUM>=1)
   6 - access("EMP_NAME_LOCATION"."FIRST_NAME"='Steven' AND "EMP_NAME_LOCATION"."LAST_NAME"='King')

That is a solution that in my opinion still can be useful in certain circumstances. The solution with just one buffer get will fail with lots of data, and lots of data pretty much is a requisite for the mview solution to be useful πŸ˜‰

SQL Mini-challenge entry: Kim Berg Hansen v4

by Kim Berg Hansen

Anything goes that is not explicitly disallowed? Okay, then let’s get down to three buffer gets πŸ˜‰

As a DBA I grant rights to HR:

grant on commit refresh to hr

And then in HR I create some materialized views and logs:

create materialized view log on employees with rowid, commit scn
   (employee_id, job_id, first_name, last_name, department_id)
   including new values;
create materialized view log on departments with rowid, commit scn
   (department_id, location_id)
   including new values;
create materialized view log on locations with rowid, commit scn
   (location_id, city)
   including new values;
create materialized view log on jobs with rowid, commit scn
   (job_id, job_title)
   including new values;
create materialized view log on job_history with rowid, commit scn
   (job_id, employee_id)
   including new values;

create materialized view emp_name_location
build immediate
refresh fast on commit
enable query rewrite
as
select e.first_name, e.last_name, l.location_id, l.city
     , e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from employees e, departments d, locations l
 where d.department_id = e.department_id
   and l.location_id = d.location_id
;

create materialized view job_cur_title_location
build immediate
refresh fast on commit
enable query rewrite
as
select j.job_title, l.location_id, l.city
     , j.rowid j_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, employees e, departments d, locations l
 where e.job_id = j.job_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
;

create materialized view job_hist_title_location
build immediate
refresh fast on commit
enable query rewrite
as
select j.job_title, l.location_id, l.city
     , j.rowid j_rowid, jh.rowid jh_rowid, e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from jobs j, job_history jh, employees e, departments d, locations l
 where jh.job_id = j.job_id
   and e.employee_id = jh.employee_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
;

create index emp_name_location_ix on emp_name_location (
   first_name, last_name, location_id, city
);

create index job_cur_title_location_ix on job_cur_title_location (
   job_title, location_id, city
);

create index job_hist_title_location_ix on job_hist_title_location (
   job_title, location_id, city
);

begin
   dbms_stats.gather_schema_stats(
      'HR'
    , estimate_percent => null
    , cascade => true
   );
end;

Now I use this query:

select /*+ gather_plan_statistics */
       l.location_id, l.city
  from employees e, departments d, 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'
 union
select l.location_id, l.city
  from jobs j, employees e, departments d, locations l
 where e.job_id = j.job_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
   and j.job_title = 'President'
 union
select l.location_id, l.city
  from jobs j, job_history jh, employees e, departments d, locations l
 where jh.job_id = j.job_id
   and e.employee_id = jh.employee_id
   and d.department_id = e.department_id
   and l.location_id = d.location_id
   and j.job_title = 'President'

And query rewrite will kick in and discover that it can use the indexes on the three materialized views:

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT UNIQUE       |                            |      1 |      8 |   259 |     3  (67)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   2 |   UNION-ALL        |                            |      1 |        |       |            |          |      2 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN| EMP_NAME_LOCATION_IX       |      1 |      1 |    31 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|*  4 |    INDEX RANGE SCAN| JOB_CUR_TITLE_LOCATION_IX  |      1 |      6 |   198 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|*  5 |    INDEX RANGE SCAN| JOB_HIST_TITLE_LOCATION_IX |      1 |      1 |    30 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------------- 

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

   3 - access("EMP_NAME_LOCATION"."FIRST_NAME"='Steven' AND "EMP_NAME_LOCATION"."LAST_NAME"='King')
   4 - access("JOB_CUR_TITLE_LOCATION"."JOB_TITLE"='President')
   5 - access("JOB_HIST_TITLE_LOCATION"."JOB_TITLE"='President')

A method like this is best used if the data is queried a lot more than it is inserted/updated/deleted.

If you use the default QUERY_REWRITE_INTEGRITY = ENFORCED, the materialized views are not used if they are stale. Since it is ON COMMIT refresh, they will be stale from the time DML is performed on the master tables until that transaction is committed. In those hopefully short and rare periods of time, queries will not be rewritten but go to the master tables and use a lot more buffer gets in those periods. If you can accept that queries do not “see” updates even in your own session until committed, you might switch to QUERY_REWRITE_INTEGRITY = STALE_TOLERATED.

One strange thing happened while I did this solution. This statement failed with “ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view” :

create materialized view emp_name_location_test2
build immediate
refresh fast on commit
enable query rewrite
as
select e.first_name, e.last_name, l.location_id, l.city
     , e.rowid e_rowid, d.rowid d_rowid, l.rowid l_rowid
  from employees e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id

Seems like ON COMMIT only works with legacy join method, not ANSI joins?

SQL Mini-challenge entry: Kim Berg Hansen v3

by Kim Berg Hansen

I just keep playing with this inspired by the other great entries πŸ™‚

Emmanuel Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to look at the constraints of the tables in order to see if some assumptions can safely be made that enables more efficient code – like for example that it can safely be assumed that every employee has a valid job_id as not null and foreign key constraints are in place.

But no matter how much I tried his code in my environment (12.1.0.1.0 on OEL – OTN virtualbox image) I could not reproduce his number of buffer gets. Then I noticed his full table scan of departments used 2 buffer gets while in my environment that was 7 buffer gets? Huh?

OK, time to try and reduce number of blocks – probably in my environment some empty blocks have been pre-allocated. So I threw some ALTER statements at the database:

alter table jobs move tablespace users;
alter table employees move tablespace users;
alter table job_history move tablespace users;
alter table departments move tablespace users;
alter table locations move tablespace users;

alter index job_id_pk rebuild;
alter index emp_department_ix rebuild;
alter index emp_email_uk rebuild;
alter index emp_emp_id_pk rebuild;
alter index emp_job_ix rebuild;
alter index emp_manager_ix rebuild;
alter index emp_name_ix rebuild;
alter index jhist_department_ix rebuild;
alter index jhist_employee_ix rebuild;
alter index jhist_emp_id_st_date_pk rebuild;
alter index jhist_job_ix rebuild;
alter index dept_id_pk rebuild;
alter index dept_location_ix rebuild;
alter index loc_city_ix rebuild;
alter index loc_country_ix rebuild;
alter index loc_id_pk rebuild;
alter index loc_state_province_ix rebuild;

begin
   dbms_stats.gather_schema_stats(
      'HR'
    , estimate_percent => null
    , cascade => true
   );
end;

And now with some judicious hinting I could get a plan like Emmanuels:

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |               |      1 |        |       |    19 (100)|          |      1 |00:00:00.01 |      17 |
|   1 |  SORT UNIQUE                               |               |      1 |      7 |   504 |    19  (37)| 00:00:01 |      1 |00:00:00.01 |      17 |
|   2 |   UNION-ALL                                |               |      1 |        |       |            |          |      1 |00:00:00.01 |      17 |
|*  3 |    HASH JOIN                               |               |      1 |      6 |   438 |    12   (0)| 00:00:01 |      1 |00:00:00.01 |      13 |
|*  4 |     HASH JOIN                              |               |      1 |      6 |   366 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|*  5 |      HASH JOIN                             |               |      1 |      6 |   324 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|   6 |       TABLE ACCESS FULL                    | JOBS          |      1 |     19 |   513 |     3   (0)| 00:00:01 |     19 |00:00:00.01 |       3 |
|   7 |       TABLE ACCESS FULL                    | EMPLOYEES     |      1 |    107 |  2889 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |       4 |
|   8 |      TABLE ACCESS FULL                     | DEPARTMENTS   |      1 |     27 |   189 |     3   (0)| 00:00:01 |     27 |00:00:00.01 |       3 |
|   9 |     TABLE ACCESS FULL                      | LOCATIONS     |      1 |     23 |   276 |     3   (0)| 00:00:01 |     23 |00:00:00.01 |       3 |
|  10 |    NESTED LOOPS                            |               |      1 |        |       |            |          |      0 |00:00:00.01 |       4 |
|  11 |     NESTED LOOPS                           |               |      1 |      1 |    66 |     7   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
|  12 |      NESTED LOOPS                          |               |      1 |      1 |    54 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
|  13 |       NESTED LOOPS                         |               |      1 |      1 |    47 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
|  14 |        NESTED LOOPS                        |               |      1 |      1 |    40 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       4 |
|* 15 |         TABLE ACCESS FULL                  | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|  16 |         TABLE ACCESS BY INDEX ROWID BATCHED| 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_PK |      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_PK    |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |
|* 22 |      INDEX UNIQUE SCAN                     | LOC_ID_PK     |      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 |
------------------------------------------------------------------------------------------------------------------------------------------------------

Except that I get 17 buffer gets, where Emmanuel get 12.

Emmanuels code full scans jobs with 2 buffers, employees with 3 buffers, departments with 2 buffers and locations with 2 buffers = 9 buffers. Then the other half of the union full scan jobs again with 2 buffers, index scan jhist_job_ix with 1 buffer which finds nothing so the rest is not accessed = 3 buffers. Total 12 buffers.

My environment adds one to every of those 5 full table scans, so I get 17 buffers rather than 12. Something is different in our environments – this is where I am not sufficient DBA to know what to look for – I am primarily a developer πŸ˜‰

Then I re-tried my “version 2” query in my “freshly rebuilt” environment. Now that my tables are free of empty blocks, I get 14 buffer gets rather than the 21 I got yesterday:

select /*+ gather_plan_statistics use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from (
   select sk.department_id
     from employees sk
    where sk.first_name = 'Steven' and sk.last_name = 'King'
      and rownum >= 1
    union
   select /*+ use_nl(jh) use_nl(pp) */
          pp.department_id
     from jobs j
     left outer join job_history jh
         on jh.job_id = j.job_id
     join employees pp
         on pp.job_id = j.job_id
         or pp.employee_id = jh.employee_id
    where j.job_title = 'President'
      and rownum >= 1
       ) e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |              |      1 |        |       |    19 (100)|          |      1 |00:00:00.01 |      14 |
|   1 |  NESTED LOOPS                                 |              |      1 |        |       |            |          |      1 |00:00:00.01 |      14 |
|   2 |   NESTED LOOPS                                |              |      1 |      1 |    32 |    19   (0)| 00:00:01 |      1 |00:00:00.01 |      13 |
|   3 |    NESTED LOOPS                               |              |      1 |      1 |    20 |    18   (0)| 00:00:01 |      1 |00:00:00.01 |      12 |
|   4 |     VIEW                                      |              |      1 |      9 |   117 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   5 |      SORT UNIQUE                              |              |      1 |      9 |   466 |     9  (78)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   6 |       UNION-ALL                               |              |      1 |        |       |            |          |      2 |00:00:00.01 |      10 |
|   7 |        COUNT                                  |              |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|*  8 |         FILTER                                |              |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|   9 |          TABLE ACCESS BY INDEX ROWID BATCHED  | EMPLOYEES    |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 10 |           INDEX RANGE SCAN                    | EMP_NAME_IX  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  11 |        COUNT                                  |              |      1 |        |       |            |          |      1 |00:00:00.01 |       8 |
|* 12 |         FILTER                                |              |      1 |        |       |            |          |      1 |00:00:00.01 |       8 |
|  13 |          NESTED LOOPS                         |              |      1 |      8 |   448 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|  14 |           NESTED LOOPS OUTER                  |              |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|* 15 |            TABLE ACCESS FULL                  | JOBS         |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|  16 |            TABLE ACCESS BY INDEX ROWID BATCHED| 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 FULL                   | EMPLOYEES    |      1 |      7 |   112 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|  19 |     TABLE ACCESS BY INDEX ROWID               | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 20 |      INDEX UNIQUE SCAN                        | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 21 |    INDEX UNIQUE SCAN                          | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  22 |   TABLE ACCESS BY INDEX ROWID                 | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Here are two full table scans – jobs using 3 buffers and employees using 4 buffers. I think if I ran this in Emmanuels environment that query would also go to 12 buffers, as Emmanuels environment can do those two full scans using 2 and 3 buffers πŸ˜‰

And just for fun – if I do the assumption that President always exists (like I tried in my first post), I can get 12 buffers :

select /*+ gather_plan_statistics use_nl(jh) use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from jobs j
  left outer join job_history jh
      on jh.job_id = j.job_id
  join employees e
      on e.job_id = j.job_id
      or e.employee_id = jh.employee_id
      or (e.first_name = 'Steven' and e.last_name = 'King')
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where j.job_title = 'President'
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |       |    23 (100)|          |      1 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                           |              |      1 |      8 |   720 |    23   (0)| 00:00:01 |      1 |00:00:00.01 |      11 |
|   3 |    NESTED LOOPS                          |              |      1 |      8 |   624 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
|   4 |     NESTED LOOPS                         |              |      1 |      8 |   568 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|   5 |      NESTED LOOPS OUTER                  |              |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  6 |       TABLE ACCESS FULL                  | JOBS         |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY  |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|*  8 |        INDEX RANGE SCAN                  | JHIST_JOB_IX |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|*  9 |      TABLE ACCESS FULL                   | EMPLOYEES    |      1 |      7 |   217 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|  10 |     TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 11 |      INDEX UNIQUE SCAN                   | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 12 |    INDEX UNIQUE SCAN                     | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  13 |   TABLE ACCESS BY INDEX ROWID            | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------------------------

Again there is the same two full table scans, so this one ought in Emmanuels environment to use only 10 buffers πŸ˜‰

This is fun, ain’t it? πŸ˜‰

SQL Mini-challenge entry: Kim Berg Hansen v2

by Kim Berg Hansen

As it seems it is ruled that inline views are allowed, I have worked a bit further on my solution with inline view:

select /*+ gather_plan_statistics use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from (
   select sk.department_id
     from employees sk
    where sk.first_name = 'Steven' and sk.last_name = 'King'
      and rownum >= 1
    union
   select /*+ use_nl(jh) use_nl(pp) */
          pp.department_id
     from jobs j
     left outer join job_history jh
         on jh.job_id = j.job_id
     join employees pp
         on pp.job_id = j.job_id
         or pp.employee_id = jh.employee_id
    where j.job_title = 'President'
      and rownum >= 1
       ) e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id

That gives me a buffer get count of 21:

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |              |      1 |        |       |    19 (100)|          |      1 |00:00:00.01 |      21 |
|   1 |  NESTED LOOPS                                 |              |      1 |        |       |            |          |      1 |00:00:00.01 |      21 |
|   2 |   NESTED LOOPS                                |              |      1 |      1 |    32 |    19   (0)| 00:00:01 |      1 |00:00:00.01 |      20 |
|   3 |    NESTED LOOPS                               |              |      1 |      1 |    20 |    18   (0)| 00:00:01 |      1 |00:00:00.01 |      19 |
|   4 |     VIEW                                      |              |      1 |      9 |   117 |     9   (0)| 00:00:01 |      1 |00:00:00.01 |      17 |
|   5 |      SORT UNIQUE                              |              |      1 |      9 |   466 |     9  (78)| 00:00:01 |      1 |00:00:00.01 |      17 |
|   6 |       UNION-ALL                               |              |      1 |        |       |            |          |      2 |00:00:00.01 |      17 |
|   7 |        COUNT                                  |              |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|*  8 |         FILTER                                |              |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|   9 |          TABLE ACCESS BY INDEX ROWID BATCHED  | EMPLOYEES    |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 10 |           INDEX RANGE SCAN                    | EMP_NAME_IX  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
|  11 |        COUNT                                  |              |      1 |        |       |            |          |      1 |00:00:00.01 |      15 |
|* 12 |         FILTER                                |              |      1 |        |       |            |          |      1 |00:00:00.01 |      15 |
|  13 |          NESTED LOOPS                         |              |      1 |      8 |   448 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |      15 |
|  14 |           NESTED LOOPS OUTER                  |              |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|* 15 |            TABLE ACCESS FULL                  | JOBS         |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|  16 |            TABLE ACCESS BY INDEX ROWID BATCHED| 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 FULL                   | EMPLOYEES    |      1 |      7 |   112 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|  19 |     TABLE ACCESS BY INDEX ROWID               | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 20 |      INDEX UNIQUE SCAN                        | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 21 |    INDEX UNIQUE SCAN                          | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  22 |   TABLE ACCESS BY INDEX ROWID                 | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

   8 - filter(ROWNUM>=1)
  10 - access("SK"."LAST_NAME"='King' AND "SK"."FIRST_NAME"='Steven')
  12 - filter(ROWNUM>=1)
  15 - filter("J"."JOB_TITLE"='President')
  17 - access("JH"."JOB_ID"="J"."JOB_ID")
  18 - filter(("PP"."JOB_ID"="J"."JOB_ID" OR "PP"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID"))
  20 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  21 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

Which is only two buffers more than the version where I assume existence of a President job πŸ˜‰

SQL Mini-challenge entry: Kim Berg Hansen

by Kim Berg Hansen

An entry for the SQL Mini-challenge of NoCOUG August 2014:

A first shot at it uses a three-part UNION approach:

select /*+ gather_plan_statistics use_nl(d) use_nl(l) */
       l.location_id, l.city
  from employees e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where e.first_name = 'Steven' and e.last_name = 'King'
   and rownum >= 1
union
select /*+ use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from jobs j
  join employees e
      on e.job_id = j.job_id
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where j.job_title = 'President'
   and rownum >= 1
union
select /*+ use_nl(jh) use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from jobs j
  join job_history jh
      on jh.job_id = j.job_id
  join employees e
      on e.employee_id = jh.employee_id
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where j.job_title = 'President'
   and rownum >= 1

The use of plenty hinting and “rownum >= 1” tricks forced this into a plan with 27 buffer gets:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |      1 |        |       |    27 (100)|          |      1 |00:00:00.01 |      27 |      3 |
|   1 |  SORT UNIQUE                                 |               |      1 |      8 |   451 |    27  (86)| 00:00:01 |      1 |00:00:00.01 |      27 |      3 |
|   2 |   UNION-ALL                                  |               |      1 |        |       |            |          |      2 |00:00:00.01 |      27 |      3 |
|   3 |    COUNT                                     |               |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      1 |
|*  4 |     FILTER                                   |               |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      1 |
|   5 |      NESTED LOOPS                            |               |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      1 |
|   6 |       NESTED LOOPS                           |               |      1 |      1 |    37 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      1 |
|   7 |        NESTED LOOPS                          |               |      1 |      1 |    25 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      1 |
|   8 |         TABLE ACCESS BY INDEX ROWID BATCHED  | EMPLOYEES     |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 |
|*  9 |          INDEX RANGE SCAN                    | EMP_NAME_IX   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      1 |
|  10 |         TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 11 |          INDEX UNIQUE SCAN                   | DEPT_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 12 |        INDEX UNIQUE SCAN                     | LOC_ID_PK     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  13 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS     |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  14 |    COUNT                                     |               |      1 |        |       |            |          |      1 |00:00:00.01 |      13 |      1 |
|* 15 |     FILTER                                   |               |      1 |        |       |            |          |      1 |00:00:00.01 |      13 |      1 |
|  16 |      NESTED LOOPS                            |               |      1 |        |       |            |          |      1 |00:00:00.01 |      13 |      1 |
|  17 |       NESTED LOOPS                           |               |      1 |      6 |   348 |    16   (0)| 00:00:01 |      1 |00:00:00.01 |      12 |      1 |
|  18 |        NESTED LOOPS                          |               |      1 |      6 |   276 |    10   (0)| 00:00:01 |      1 |00:00:00.01 |      11 |      1 |
|  19 |         NESTED LOOPS                         |               |      1 |      6 |   234 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |      1 |
|* 20 |          TABLE ACCESS FULL                   | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |
|  21 |          TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES     |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 |
|* 22 |           INDEX RANGE SCAN                   | EMP_JOB_IX    |      1 |      6 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      1 |
|  23 |         TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 24 |          INDEX UNIQUE SCAN                   | DEPT_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 25 |        INDEX UNIQUE SCAN                     | LOC_ID_PK     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  26 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS     |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  27 |    COUNT                                     |               |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |      1 |
|* 28 |     FILTER                                   |               |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |      1 |
|  29 |      NESTED LOOPS                            |               |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |      1 |
|  30 |       NESTED LOOPS                           |               |      1 |      1 |    66 |     7   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |      1 |
|  31 |        NESTED LOOPS                          |               |      1 |      1 |    54 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |      1 |
|  32 |         NESTED LOOPS                         |               |      1 |      1 |    47 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |      1 |
|  33 |          NESTED LOOPS                        |               |      1 |      1 |    40 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |      1 |
|* 34 |           TABLE ACCESS FULL                  | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |
|  35 |           TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY   |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |      1 |
|* 36 |            INDEX RANGE SCAN                  | JHIST_JOB_IX  |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |      1 |
|  37 |          TABLE ACCESS BY INDEX ROWID         | EMPLOYEES     |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 38 |           INDEX UNIQUE SCAN                  | EMP_EMP_ID_PK |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |
|  39 |         TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS   |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 40 |          INDEX UNIQUE SCAN                   | DEPT_ID_PK    |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |
|* 41 |        INDEX UNIQUE SCAN                     | LOC_ID_PK     |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |
|  42 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS     |      0 |      1 |    12 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------                                       

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

   4 - filter(ROWNUM>=1)
   9 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  11 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  12 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
  15 - filter(ROWNUM>=1)
  20 - filter("J"."JOB_TITLE"='President')
  22 - access("E"."JOB_ID"="J"."JOB_ID")
  24 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  25 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
  28 - filter(ROWNUM>=1)
  34 - filter("J"."JOB_TITLE"='President')
  36 - access("JH"."JOB_ID"="J"."JOB_ID")
  38 - access("E"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
  40 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  41 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

With a little rewriting, it can be turned into a two-part UNION:

select /*+ gather_plan_statistics use_nl(d) use_nl(l) */
       l.location_id, l.city
  from employees e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where e.first_name = 'Steven' and e.last_name = 'King'
   and rownum >= 1
union
select /*+ use_nl(jh) use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from jobs j
  left outer join job_history jh
      on jh.job_id = j.job_id
  join employees e
      on e.job_id = j.job_id
      or e.employee_id = jh.employee_id
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where j.job_title = 'President'
   and rownum >= 1

Which yields a plan with 25 buffer gets:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |              |      1 |        |       |    27 (100)|          |      1 |00:00:00.01 |      25 |      5 |
|   1 |  SORT UNIQUE                                 |              |      1 |      9 |   637 |    27  (86)| 00:00:01 |      1 |00:00:00.01 |      25 |      5 |
|   2 |   UNION-ALL                                  |              |      1 |        |       |            |          |      2 |00:00:00.01 |      25 |      5 |
|   3 |    COUNT                                     |              |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      0 |
|*  4 |     FILTER                                   |              |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      0 |
|   5 |      NESTED LOOPS                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      0 |
|   6 |       NESTED LOOPS                           |              |      1 |      1 |    37 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      0 |
|   7 |        NESTED LOOPS                          |              |      1 |      1 |    25 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      0 |
|   8 |         TABLE ACCESS BY INDEX ROWID BATCHED  | EMPLOYEES    |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|*  9 |          INDEX RANGE SCAN                    | EMP_NAME_IX  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  10 |         TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 11 |          INDEX UNIQUE SCAN                   | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 12 |        INDEX UNIQUE SCAN                     | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  13 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  14 |    COUNT                                     |              |      1 |        |       |            |          |      1 |00:00:00.01 |      19 |      5 |
|* 15 |     FILTER                                   |              |      1 |        |       |            |          |      1 |00:00:00.01 |      19 |      5 |
|  16 |      NESTED LOOPS                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |      19 |      5 |
|  17 |       NESTED LOOPS                           |              |      1 |      8 |   600 |    23   (0)| 00:00:01 |      1 |00:00:00.01 |      18 |      5 |
|  18 |        NESTED LOOPS                          |              |      1 |      8 |   504 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |      17 |      5 |
|  19 |         NESTED LOOPS                         |              |      1 |      8 |   448 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |      15 |      5 |
|  20 |          NESTED LOOPS OUTER                  |              |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |      0 |
|* 21 |           TABLE ACCESS FULL                  | JOBS         |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |
|  22 |           TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY  |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |      0 |
|* 23 |            INDEX RANGE SCAN                  | JHIST_JOB_IX |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |      0 |
|* 24 |          TABLE ACCESS FULL                   | EMPLOYEES    |      1 |      7 |   112 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      5 |
|  25 |         TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 26 |          INDEX UNIQUE SCAN                   | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 27 |        INDEX UNIQUE SCAN                     | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  28 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------                                        

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

   4 - filter(ROWNUM>=1)
   9 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  11 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  12 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
  15 - filter(ROWNUM>=1)
  21 - filter("J"."JOB_TITLE"='President')
  23 - access("JH"."JOB_ID"="J"."JOB_ID")
  24 - filter(("E"."JOB_ID"="J"."JOB_ID" OR "E"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID"))
  26 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  27 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

Then I noticed my second attempt due to hinting did a nested loop with a full table scan of employee – that ought to be bad for sure. So I deleted the one hint that was too much:

select /*+ gather_plan_statistics use_nl(d) use_nl(l) */
       l.location_id, l.city
  from employees e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where e.first_name = 'Steven' and e.last_name = 'King'
   and rownum >= 1
union
select /*+ use_nl(jh) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from jobs j
  left outer join job_history jh
      on jh.job_id = j.job_id
  join employees e
      on e.job_id = j.job_id
      or e.employee_id = jh.employee_id
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where j.job_title = 'President'
   and rownum >= 1

This time I got rid of my nested loop with full table scan, but at the cost of using 30 buffer gets?

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |              |      1 |        |       |    22 (100)|          |      1 |00:00:00.01 |      30 |      5 |
|   1 |  SORT UNIQUE                                 |              |      1 |      9 |   637 |    22  (82)| 00:00:01 |      1 |00:00:00.01 |      30 |      5 |
|   2 |   UNION-ALL                                  |              |      1 |        |       |            |          |      2 |00:00:00.01 |      30 |      5 |
|   3 |    COUNT                                     |              |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      0 |
|*  4 |     FILTER                                   |              |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      0 |
|   5 |      NESTED LOOPS                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |      0 |
|   6 |       NESTED LOOPS                           |              |      1 |      1 |    37 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      0 |
|   7 |        NESTED LOOPS                          |              |      1 |      1 |    25 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      0 |
|   8 |         TABLE ACCESS BY INDEX ROWID BATCHED  | EMPLOYEES    |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|*  9 |          INDEX RANGE SCAN                    | EMP_NAME_IX  |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  10 |         TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 11 |          INDEX UNIQUE SCAN                   | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 12 |        INDEX UNIQUE SCAN                     | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  13 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  14 |    COUNT                                     |              |      1 |        |       |            |          |      1 |00:00:00.01 |      24 |      5 |
|* 15 |     FILTER                                   |              |      1 |        |       |            |          |      1 |00:00:00.01 |      24 |      5 |
|  16 |      NESTED LOOPS                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |      24 |      5 |
|  17 |       NESTED LOOPS                           |              |      1 |      8 |   600 |    18   (0)| 00:00:01 |      1 |00:00:00.01 |      23 |      5 |
|* 18 |        HASH JOIN                             |              |      1 |      8 |   504 |    10   (0)| 00:00:01 |      1 |00:00:00.01 |      22 |      5 |
|  19 |         NESTED LOOPS                         |              |      1 |     34 |  1598 |     7   (0)| 00:00:01 |     27 |00:00:00.01 |      15 |      5 |
|  20 |          NESTED LOOPS OUTER                  |              |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |      0 |
|* 21 |           TABLE ACCESS FULL                  | JOBS         |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |
|  22 |           TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY  |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |      0 |
|* 23 |            INDEX RANGE SCAN                  | JHIST_JOB_IX |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |      0 |
|  24 |          TABLE ACCESS FULL                   | DEPARTMENTS  |      1 |     27 |   189 |     3   (0)| 00:00:01 |     27 |00:00:00.01 |       7 |      5 |
|  25 |         TABLE ACCESS FULL                    | EMPLOYEES    |      1 |    107 |  1712 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |       7 |      0 |
|* 26 |        INDEX UNIQUE SCAN                     | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  27 |       TABLE ACCESS BY INDEX ROWID            | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------                                        

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

   4 - filter(ROWNUM>=1)
   9 - access("E"."LAST_NAME"='King' AND "E"."FIRST_NAME"='Steven')
  11 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  12 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
  15 - filter(ROWNUM>=1)
  18 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter(("E"."JOB_ID"="J"."JOB_ID" OR "E"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID"))
  21 - filter("J"."JOB_TITLE"='President')
  23 - access("JH"."JOB_ID"="J"."JOB_ID")
  26 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

Huh? Well, skip that approach all together and rewrite it again without any UNION:

select /*+ gather_plan_statistics use_nl(jh) use_nl(j) use_nl(d) use_nl(l) */
       distinct l.location_id, l.city
  from employees e
  left outer join job_history jh
      on jh.employee_id = e.employee_id
  left outer join jobs j2
      on j2.job_id = jh.job_id
  join jobs j
      on j.job_id = e.job_id
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where (e.first_name = 'Steven' and e.last_name = 'King')
    or j.job_title = 'President'
    or j2.job_title = 'President'

This time I get 24 buffers:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                   |      1 |        |       |    37 (100)|          |      1 |00:00:00.01 |      24 |      3 |
|   1 |  HASH UNIQUE                               |                   |      1 |     11 |  1287 |    37   (0)| 00:00:01 |      1 |00:00:00.01 |      24 |      3 |
|   2 |   NESTED LOOPS                             |                   |      1 |        |       |            |          |      1 |00:00:00.01 |      24 |      3 |
|   3 |    NESTED LOOPS                            |                   |      1 |     11 |  1287 |    37   (0)| 00:00:01 |      1 |00:00:00.01 |      23 |      3 |
|   4 |     NESTED LOOPS                           |                   |      1 |     11 |  1155 |    26   (0)| 00:00:01 |      1 |00:00:00.01 |      22 |      3 |
|*  5 |      FILTER                                |                   |      1 |        |       |            |          |      1 |00:00:00.01 |      20 |      3 |
|*  6 |       HASH JOIN RIGHT OUTER                |                   |      1 |     11 |  1078 |    15   (0)| 00:00:01 |    110 |00:00:00.01 |      20 |      3 |
|   7 |        TABLE ACCESS FULL                   | JOBS              |      1 |     19 |   513 |     3   (0)| 00:00:01 |     19 |00:00:00.01 |       7 |      0 |
|   8 |        NESTED LOOPS OUTER                  |                   |      1 |    110 |  7810 |    12   (0)| 00:00:01 |    110 |00:00:00.01 |      13 |      3 |
|   9 |         MERGE JOIN                         |                   |      1 |    107 |  6206 |     5   (0)| 00:00:01 |    107 |00:00:00.01 |       9 |      1 |
|  10 |          TABLE ACCESS BY INDEX ROWID       | JOBS              |      1 |     19 |   513 |     2   (0)| 00:00:01 |     19 |00:00:00.01 |       2 |      1 |
|  11 |           INDEX FULL SCAN                  | JOB_ID_PK         |      1 |     19 |       |     1   (0)| 00:00:01 |     19 |00:00:00.01 |       1 |      1 |
|* 12 |          SORT JOIN                         |                   |     19 |    107 |  3317 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |       7 |      0 |
|  13 |           TABLE ACCESS FULL                | EMPLOYEES         |      1 |    107 |  3317 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |       7 |      0 |
|  14 |         TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY       |    107 |      1 |    13 |     1   (0)| 00:00:01 |     10 |00:00:00.01 |       4 |      2 |
|* 15 |          INDEX RANGE SCAN                  | JHIST_EMPLOYEE_IX |    107 |      1 |       |     0   (0)|          |     10 |00:00:00.01 |       3 |      1 |
|  16 |      TABLE ACCESS BY INDEX ROWID           | DEPARTMENTS       |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 17 |       INDEX UNIQUE SCAN                    | DEPT_ID_PK        |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 18 |     INDEX UNIQUE SCAN                      | LOC_ID_PK         |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  19 |    TABLE ACCESS BY INDEX ROWID             | LOCATIONS         |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                     

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

   5 - filter((("E"."FIRST_NAME"='Steven' AND "E"."LAST_NAME"='King') OR "J"."JOB_TITLE"='President' OR "J2"."JOB_TITLE"='President'))
   6 - access("J2"."JOB_ID"="JH"."JOB_ID")
  12 - access("J"."JOB_ID"="E"."JOB_ID")
       filter("J"."JOB_ID"="E"."JOB_ID")
  15 - access("JH"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
  17 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  18 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

That’s better πŸ™‚ That’ll be my entry into the competition with just 24 buffer gets…

I guess inline viewsΒ fall into the category subquery and thus not allowed in the competition, but if it were allowed, I could do this:

select /*+ gather_plan_statistics use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from (
   select sk.department_id
     from employees sk
    where sk.first_name = 'Steven' and sk.last_name = 'King'
      and rownum >= 1
   union
   select /*+ use_nl(p) */
          p.department_id
     from jobs j
     join employees p
         on p.job_id = j.job_id
    where j.job_title = 'President'
      and rownum >= 1
   union
   select /*+ use_nl(jh) use_nl(pp) */
          pp.department_id
     from jobs j
     join job_history jh
         on jh.job_id = j.job_id
     join employees pp
         on pp.employee_id = jh.employee_id
    where j.job_title = 'President'
      and rownum >= 1
  ) e
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id

Which gets me down to 23 buffer gets:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |               |      1 |        |       |    27 (100)|          |      1 |00:00:00.01 |      23 |      1 |
|   1 |  NESTED LOOPS                                  |               |      1 |        |       |            |          |      1 |00:00:00.01 |      23 |      1 |
|   2 |   NESTED LOOPS                                 |               |      1 |      8 |   256 |    27   (0)| 00:00:01 |      1 |00:00:00.01 |      22 |      1 |
|   3 |    NESTED LOOPS                                |               |      1 |      8 |   160 |    19   (0)| 00:00:01 |      1 |00:00:00.01 |      21 |      1 |
|   4 |     VIEW                                       |               |      1 |      8 |   104 |    11   (0)| 00:00:01 |      1 |00:00:00.01 |      19 |      1 |
|   5 |      SORT UNIQUE                               |               |      1 |      8 |   299 |    11  (82)| 00:00:01 |      1 |00:00:00.01 |      19 |      1 |
|   6 |       UNION-ALL                                |               |      1 |        |       |            |          |      2 |00:00:00.01 |      19 |      1 |
|   7 |        COUNT                                   |               |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |      0 |
|*  8 |         FILTER                                 |               |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |      0 |
|   9 |          TABLE ACCESS BY INDEX ROWID BATCHED   | EMPLOYEES     |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 10 |           INDEX RANGE SCAN                     | EMP_NAME_IX   |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  11 |        COUNT                                   |               |      1 |        |       |            |          |      1 |00:00:00.01 |       9 |      1 |
|* 12 |         FILTER                                 |               |      1 |        |       |            |          |      1 |00:00:00.01 |       9 |      1 |
|  13 |          NESTED LOOPS                          |               |      1 |        |       |            |          |      1 |00:00:00.01 |       9 |      1 |
|  14 |           NESTED LOOPS                         |               |      1 |      6 |   234 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |      1 |
|* 15 |            TABLE ACCESS FULL                   | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |
|* 16 |            INDEX RANGE SCAN                    | EMP_JOB_IX    |      1 |      6 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      1 |
|  17 |           TABLE ACCESS BY INDEX ROWID          | EMPLOYEES     |      1 |      6 |    72 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
|  18 |        COUNT                                   |               |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |      0 |
|* 19 |         FILTER                                 |               |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |      0 |
|  20 |          NESTED LOOPS                          |               |      1 |        |       |            |          |      0 |00:00:00.01 |       8 |      0 |
|  21 |           NESTED LOOPS                         |               |      1 |      1 |    47 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |      0 |
|  22 |            NESTED LOOPS                        |               |      1 |      1 |    40 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       8 |      0 |
|* 23 |             TABLE ACCESS FULL                  | JOBS          |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |
|  24 |             TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY   |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |      0 |
|* 25 |              INDEX RANGE SCAN                  | JHIST_JOB_IX  |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |      0 |
|* 26 |            INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |
|  27 |           TABLE ACCESS BY INDEX ROWID          | EMPLOYEES     |      0 |      1 |     7 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|  28 |     TABLE ACCESS BY INDEX ROWID                | DEPARTMENTS   |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|* 29 |      INDEX UNIQUE SCAN                         | DEPT_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|* 30 |    INDEX UNIQUE SCAN                           | LOC_ID_PK     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |      0 |
|  31 |   TABLE ACCESS BY INDEX ROWID                  | LOCATIONS     |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                     

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

   8 - filter(ROWNUM>=1)
  10 - access("SK"."LAST_NAME"='King' AND "SK"."FIRST_NAME"='Steven')
  12 - filter(ROWNUM>=1)
  15 - filter("J"."JOB_TITLE"='President')
  16 - access("P"."JOB_ID"="J"."JOB_ID")
  19 - filter(ROWNUM>=1)
  23 - filter("J"."JOB_TITLE"='President')
  25 - access("JH"."JOB_ID"="J"."JOB_ID")
  26 - access("PP"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID")
  29 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  30 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

Or even better if it were possible to assume, that table JOBS contain one record with job_title=’President’. Might be a reasonable assumption in real life as the question the query should answer does not make much sense if no ‘President’ job exists πŸ™‚ Of course it can’t be in the competition, but if such an assumption was allowed I could write:

select /*+ gather_plan_statistics use_nl(jh) use_nl(e) use_nl(d) use_nl(l) */
       l.location_id, l.city
  from jobs j
  left outer join job_history jh
      on jh.job_id = j.job_id
  join employees e
      on e.job_id = j.job_id
      or e.employee_id = jh.employee_id
      or (e.first_name = 'Steven' and e.last_name = 'King')
  join departments d
      on d.department_id = e.department_id
  join locations l
      on l.location_id = d.location_id
 where j.job_title = 'President'

And get the buffer gets all the way down to 19:

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |       |    23 (100)|          |      1 |00:00:00.01 |      19 |
|   1 |  NESTED LOOPS                            |              |      1 |        |       |            |          |      1 |00:00:00.01 |      19 |
|   2 |   NESTED LOOPS                           |              |      1 |      8 |   720 |    23   (0)| 00:00:01 |      1 |00:00:00.01 |      18 |
|   3 |    NESTED LOOPS                          |              |      1 |      8 |   624 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |      17 |
|   4 |     NESTED LOOPS                         |              |      1 |      8 |   568 |     7   (0)| 00:00:01 |      1 |00:00:00.01 |      15 |
|   5 |      NESTED LOOPS OUTER                  |              |      1 |      1 |    40 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|*  6 |       TABLE ACCESS FULL                  | JOBS         |      1 |      1 |    27 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| JOB_HISTORY  |      1 |      1 |    13 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       1 |
|*  8 |        INDEX RANGE SCAN                  | JHIST_JOB_IX |      1 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       1 |
|*  9 |      TABLE ACCESS FULL                   | EMPLOYEES    |      1 |      7 |   217 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|  10 |     TABLE ACCESS BY INDEX ROWID          | DEPARTMENTS  |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 11 |      INDEX UNIQUE SCAN                   | DEPT_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|* 12 |    INDEX UNIQUE SCAN                     | LOC_ID_PK    |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|  13 |   TABLE ACCESS BY INDEX ROWID            | LOCATIONS    |      1 |      1 |    12 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------------------------                                                     

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

   6 - filter("J"."JOB_TITLE"='President')
   8 - access("JH"."JOB_ID"="J"."JOB_ID")
   9 - filter(("E"."JOB_ID"="J"."JOB_ID" OR "E"."EMPLOYEE_ID"="JH"."EMPLOYEE_ID" OR ("E"."FIRST_NAME"='Steven' AND
              "E"."LAST_NAME"='King')))
  11 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  12 - access("L"."LOCATION_ID"="D"."LOCATION_ID")

But the last two won’t strictly meet the competition criteria, so 24 buffer gets will be what I can achieve πŸ˜‰