Northern California Oracle Users Group

Home » SQL Challenge » SQL Mini-challenge entry: 1 buffer for 1st execution

SQL Mini-challenge entry: 1 buffer for 1st execution

by Vlado Barun

RESULTS

This solution has buffers=1 for the first execution, and buffers=0 for the second execution (using results cache as Timo Raitalaakso demonstrated previously).

SUMMARY

The solution uses the query rewrite feature to retrieve the rows from an index on a materialized view. Since the index has blevel=0 and leaf_blocks=1, the execution plans shows buffers=1.

DISCLAIMER

1. I do NOT consider this solution appropriate for PRODUCTION environments. It is too narrow and complex and thus expensive to maintain.
2. I work for Oracle Corporation (not that it matters for this, but thought I should mention it, just in case).

SOLUTION

HR> /********** Prerequisites *************
HR> 1. ASSM tablespace 16k or 32k blocksize i.e. create tablespace bb blocksize 32k;
HR> 2. buffer cache with the above blocksize i.e. alter system set DB_32K_CACHE_SIZE=100 scope=memory sid='MYSID';
HR> */
HR>
HR>
HR> set appinfo off
HR> set serverout off
HR>
HR> alter session set statistics_level=all;

Session altered.

HR>
HR> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

HR>
HR>
HR> create materialized view employee_job_history
  2  (
  3  	   current_job_title, last_name, first_name, past_job_title, city, location_id
  4  )
  5  enable query rewrite
  6  as
  7  select  ej.job_title as current_job_title, last_name, first_name, jh.job_title as past_job_title, city, l.location_Id as location_id
  8    from  locations	 l
  9  	   , departments d
 10  	   , (
 11  		    select department_id, job_title
 12  		      from jobs        j
 13  			 , job_history jh
 14  		     where jh.job_id = j.job_id
 15  	     ) jh -- past job titles by department
 16  	   , (
 17  		    select department_id, job_title, last_name, first_name
 18  		      from jobs      j
 19  			 , employees e
 20  		     where e.job_id = j.job_id
 21  	     ) ej -- current job title, employee name and department_id for each current employee
 22    where l.location_id   = d.location_id
 23  	 and d.department_id = jh.department_id (+)
 24  	 and d.department_id = ej.department_id (+)
 25  ;

Materialized view created.

HR>
HR>
HR> create index i1 on employee_job_history
  2  (
  3    current_job_title, past_job_title, last_name, first_name, location_id, city
  4  )
  5  tablespace bb
  6  ;

Index created.

HR>
HR> exec dbms_stats.gather_table_stats(user, 'EMPLOYEE_JOB_HISTORY', no_invalidate=>false);

PL/SQL procedure successfully completed.

HR>
HR> select blevel, leaf_blocks from dba_indexes where index_name='I1';

    BLEVEL LEAF_BLOCKS
---------- -----------
         0           1

HR>
HR> alter session set result_cache_mode = force;

Session altered.

HR>
HR> select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as location_id, city
  2    from  locations	 l
  3  	   , departments d
  4  	   , (
  5  		    select department_id, job_title
  6  		      from jobs        j
  7  			 , job_history jh
  8  		     where jh.job_id = j.job_id
  9  	     ) jh -- past job titles by department
 10  	   , (
 11  		    select department_id, job_title, last_name, first_name
 12  		      from jobs      j
 13  			 , employees e
 14  		     where e.job_id = j.job_id
 15  	     ) ej -- current job title, employee name and department_id for each current employee
 16    where l.location_id   = d.location_id
 17  	 and d.department_id = jh.department_id (+)
 18  	 and d.department_id = ej.department_id  (+)
 19  	 and (
 20  		   jh.job_title   = 'President' --    past president
 21  	       or (				-- or Steven Kind
 22  		       last_name  = 'King'
 23  		   and first_name = 'Steve'
 24  		  )
 25  	       or  ej.job_title   = 'President' -- or current president
 26  	     )
 27  ;

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

HR>
HR> select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------
SQL_ID  6qckafghm6rf2, child number 2
-------------------------------------
select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as
location_id, city   from  locations   l       , departments d       , (
               select department_id, job_title                  from
jobs        j                     , job_history jh
where jh.job_id = j.job_id         ) jh -- past job titles by
department       , (                select department_id, job_title,
last_name, first_name                  from jobs      j
    , employees e                 where e.job_id = j.job_id         )
ej -- current job title, employee name and department_id for each
current employee   where l.location_id   = d.location_id     and
d.department_id = jh.department_id (+)     and d.department_id =
ej.department_id  (+)     and (               jh.job_title   =
'President' --    past president           or (
    -- or Steven Kind                   last_name  = 'King'
  and first_name = 'Steve'              )

Plan hash value: 3143307749

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |      1 |        |      1 |00:00:00.01 |       1 |       |       |          |
|   1 |  RESULT CACHE     | 90fk8p43hbyj77p9wy91s02f5k |      1 |        |      1 |00:00:00.01 |       1 |       |       |          |
|   2 |   HASH UNIQUE     |                            |      1 |     32 |      1 |00:00:00.01 |       1 |  1646K|  1646K|  461K (0)|
|*  3 |    INDEX FULL SCAN| I1                         |      1 |     32 |      2 |00:00:00.01 |       1 |  1025K|  1025K|          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("EMPLOYEE_JOB_HISTORY"."PAST_JOB_TITLE"='President' OR "EMPLOYEE_JOB_HISTORY"."CURRENT_JOB_TITLE"='President'
              OR ("EMPLOYEE_JOB_HISTORY"."LAST_NAME"='King' AND "EMPLOYEE_JOB_HISTORY"."FIRST_NAME"='Steve')))

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

40 rows selected.

HR>
HR> -- execute query one more time for result caching to kick in
HR> select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as location_id, city
  2    from  locations	 l
  3  	   , departments d
  4  	   , (
  5  		    select department_id, job_title
  6  		      from jobs        j
  7  			 , job_history jh
  8  		     where jh.job_id = j.job_id
  9  	     ) jh -- past job titles by department
 10  	   , (
 11  		    select department_id, job_title, last_name, first_name
 12  		      from jobs      j
 13  			 , employees e
 14  		     where e.job_id = j.job_id
 15  	     ) ej -- current job title, employee name and department_id for each current employee
 16    where l.location_id   = d.location_id
 17  	 and d.department_id = jh.department_id (+)
 18  	 and d.department_id = ej.department_id (+)
 19  	 and (
 20  		   jh.job_title   = 'President' --    past president
 21  	       or (				-- or Steven Kind
 22  		       last_name  = 'King'
 23  		   and first_name = 'Steve'
 24  		  )
 25  	       or  ej.job_title   = 'President' -- or current president
 26  	     )
 27  ;

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

HR>
HR> select * from table(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------
SQL_ID  0u3wxbaa19msv, child number 0
-------------------------------------
select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as
location_id, city   from  locations   l       , departments d       , (
               select department_id, job_title                  from
jobs        j                     , job_history jh
where jh.job_id = j.job_id         ) jh -- past job titles by
department       , (                select department_id, job_title,
last_name, first_name                  from jobs      j
    , employees e                 where e.job_id = j.job_id         )
ej -- current job title, employee name and department_id for each
current employee   where l.location_id   = d.location_id     and
d.department_id = jh.department_id (+)     and d.department_id =
ej.department_id (+)     and (               jh.job_title   =
'President' --    past president           or (
    -- or Steven Kind                   last_name  = 'King'
  and first_name = 'Steve'              )

Plan hash value: 3143307749

------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |      1 |        |      1 |00:00:00.01 |
|   1 |  RESULT CACHE     | 90fk8p43hbyj77p9wy91s02f5k |      1 |        |      1 |00:00:00.01 |
|   2 |   HASH UNIQUE     |                            |      0 |     32 |      0 |00:00:00.01 |
|*  3 |    INDEX FULL SCAN| I1                         |      0 |     32 |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------

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

   3 - filter(("EMPLOYEE_JOB_HISTORY"."PAST_JOB_TITLE"='President' OR
              "EMPLOYEE_JOB_HISTORY"."CURRENT_JOB_TITLE"='President' OR
              ("EMPLOYEE_JOB_HISTORY"."LAST_NAME"='King' AND
              "EMPLOYEE_JOB_HISTORY"."FIRST_NAME"='Steve')))

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

42 rows selected.

HR>
HR> spool off
Advertisements

8 Comments

  1. Iggy Fernandez says:

    Would it be possible to modify the materialized view so that is fast-refreshable on commit?

    Like

  2. Iggy Fernandez says:

    Is a tablespace with a 32 KB block size required in this case. The index is small enough, isn’t it?

    Like

  3. Iggy Fernandez says:

    There is some problem with the modified query because it returns different results than the challenge query after the data is modified.

    [oracle@localhost ~]$ sqlplus hr/hr @test.sql
    
    SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 17 13:11:04 2014
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set linesize 200
    SQL> set trimspool on
    SQL> set pagesize 1000
    SQL> set tab off
    SQL> spool test.log
    SQL> 
    SQL> -- modify the data
    SQL> update employees
      2  set
      3    first_name = 'Stevie',
      4    last_name = 'Wonder',
      5    hire_date = sysdate,
      6    job_id = 'AD_VP'
      7  where employee_id = 100;
    
    1 row updated.
    
    SQL> 
    SQL> update employees
      2  set
      3    first_name = 'Steven',
      4    last_name = 'King'
      5  where employee_id = 203;
    
    1 row updated.
    
    SQL> 
    SQL> update employees
      2  set
      3    job_id = 'AD_PRES',
      4    hire_date = sysdate
      5  where employee_id = 204;
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> select e.employee_id, e.department_id, l.location_id
      2  from employees e, departments d, locations l
      3  where e.department_id = d.department_id
      4  and d.location_id = l.location_id
      5  and e.employee_id in (100, 203, 204);
    
    EMPLOYEE_ID DEPARTMENT_ID LOCATION_ID
    ----------- ------------- -----------
            100            90        1700
            203            40        2400
            204            70        2700
    
    SQL> 
    SQL> -- show the results of the challenge query
    SQL> SELECT l.location_id, l.city
      2  FROM locations l
      3  WHERE EXISTS (
      4    SELECT * FROM departments d, employees e, jobs j
      5    WHERE d.location_id = l.location_id
      6    AND e.department_id = d.department_id
      7    AND j.job_id = e.job_id
      8    AND (
      9      (e.first_name = 'Steven' AND e.last_name = 'King')
     10      OR j.job_title  = 'President'
     11      OR EXISTS (
     12        SELECT * FROM job_history jh, jobs j
     13        WHERE jh.employee_id = e.employee_id
     14        AND j.job_id = jh.job_id
     15        AND j.job_title = 'President'
     16      )
     17    )
     18  );
    
    LOCATION_ID CITY
    ----------- ------------------------------
           2400 London
           1700 Seattle
           2700 Munich
    
    SQL> 
    SQL> create materialized view employee_job_history
      2   (
      3         current_job_title, last_name, first_name, past_job_title, city, location_id
      4   )
      5   enable query rewrite
      6   as
      7   select  ej.job_title as current_job_title, last_name, first_name, jh.job_title as past_job_title, city, l.location_Id as location_id
      8     from  locations   l
      9         , departments d
     10         , (
     11              select department_id, job_title
     12                from jobs        j
     13               , job_history jh
     14               where jh.job_id = j.job_id
     15           ) jh -- past job titles by department
     16         , (
     17              select department_id, job_title, last_name, first_name
     18                from jobs      j
     19               , employees e
     20               where e.job_id = j.job_id
     21           ) ej -- current job title, employee name and department_id for each current employee
     22     where l.location_id   = d.location_id
     23       and d.department_id = jh.department_id (+)
     24       and d.department_id = ej.department_id (+)
     25   ;
    
    Materialized view created.
    
    SQL> 
    SQL> create index i1 on employee_job_history
      2   (
      3     current_job_title, past_job_title, last_name, first_name, location_id, city
      4   )
      5   -- tablespace bb
      6   ;
    
    Index created.
    
    SQL> 
    SQL> exec dbms_stats.gather_table_stats(user, 'EMPLOYEE_JOB_HISTORY', no_invalidate=>false);
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as location_id, city
      2     from  locations   l
      3         , departments d
      4         , (
      5              select department_id, job_title
      6                from jobs        j
      7               , job_history jh
      8               where jh.job_id = j.job_id
      9           ) jh -- past job titles by department
     10         , (
     11              select department_id, job_title, last_name, first_name
     12                from jobs      j
     13               , employees e
     14               where e.job_id = j.job_id
     15           ) ej -- current job title, employee name and department_id for each current employee
     16     where l.location_id   = d.location_id
     17       and d.department_id = jh.department_id (+)
     18       and d.department_id = ej.department_id  (+)
     19       and (
     20             jh.job_title   = 'President' --    past president
     21             or (             -- or Steven Kind
     22                 last_name  = 'King'
     23             and first_name = 'Steve'
     24            )
     25             or  ej.job_title   = 'President' -- or current president
     26           )
     27   ;
    
    LOCATION_ID CITY
    ----------- ------------------------------
           2700 Munich
           1700 Seattle
    
    SQL> 
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    

    Like

  4. vbarun says:

    The reason for the difference is because of a type in my solution:
    In my query I’m filtering on ” … and first_name = ‘Steve'”. The letter n is missing in the first name value. It should be
    ” … and first_name = ‘Steven'”

    Like

    • vbarun says:
      hr> select  distinct /*+ REWRITE (EMPLOYEE_JOB_HISTORY) */ l.location_Id as location_id, city
        2    from  locations   l
        3        , departments d
        4        , (
        5                 select department_id, job_title
        6                   from jobs        j
        7                      , job_history jh
        8                  where jh.job_id = j.job_id
        9          ) jh -- past job titles by department
       10        , (
       11                 select department_id, job_title, last_name, first_name
       12                   from jobs      j
       13                      , employees e
       14                  where e.job_id = j.job_id
       15          ) ej -- current job title, employee name and department_id for each current employee
       16    where l.location_id   = d.location_id
       17      and d.department_id = jh.department_id (+)
       18      and d.department_id = ej.department_id (+)
       19      and (
       20                jh.job_title   = 'President' --    past president
       21            or (                             -- or Steven Kind
       22                    last_name  = 'King'
       23                and first_name = 'Steven'
       24               )
       25            or  ej.job_title   = 'President' -- or current president
       26          )
       27  ;
      
      LOCATION_ID CITY
      ----------- ------------------------------
             2700 Munich
             1700 Seattle
             2400 London
      

      Like

  5. Iggy Fernandez says:

    Could the solution be tweaked so that the materialized view is fast-refreshable on commit?

    SQL> create materialized view employee_job_history
      2  (
      3        current_job_title, last_name, first_name, past_job_title, city, location_id
      4  )
      5  refresh fast on commit
      6  enable query rewrite
      7  as
      8  select  ej.job_title as current_job_title, last_name, first_name, jh.job_title as past_job_title, city, l.location_Id as location_id
      9    from  locations   l
     10        , departments d
     11        , (
     12             select department_id, job_title
     13               from jobs        j
     14              , job_history jh
     15              where jh.job_id = j.job_id
     16          ) jh -- past job titles by department
     17        , (
     18             select department_id, job_title, last_name, first_name
     19               from jobs      j
     20              , employees e
     21              where e.job_id = j.job_id
     22          ) ej -- current job title, employee name and department_id for each current employee
     23    where l.location_id   = d.location_id
     24      and d.department_id = jh.department_id (+)
     25      and d.department_id = ej.department_id (+)
     26  ;
      from  locations   l
            *
    ERROR at line 9:
    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
    

    Like

  6. vbarun says:

    Maybe, but it is not part of the requirement 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: