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 😉