Mohamed Houri’s Oracle Notes

October 1, 2014

Partition by virtual column

Filed under: Oracle — hourim @ 3:19 pm

Almost a year ago I trouble-shooted a query performance issue which was mainly due to a high number of logical I/O consumed by a local non prefixed index accessing a range partitioned table without eliminating partitions (partition range all). This trouble shooting issue paved the way to the partitioned index: global or local article via which the designer of the “culprit” index has perfectly learnt the lesson.

Everyone was back to its daily work until last week when I was handled a complex query performing badly. Here below, reduced to its problematic part only, is the corresponding execution plan taken from memory

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |       |
|   1 |  TEMP TABLE TRANSFORMATION           |                             |       |       |       |
|   2 |   LOAD AS SELECT                     |                             |       |       |       |
|   3 |    PARTITION LIST ALL                |                             |     1 |     1 |   367 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| TAB_LOG_ENTRY               |     1 |     1 |   367 |
|*  5 |      INDEX RANGE SCAN                | LOCAL_N_PREFIX_INDEX        |     2 |     1 |   367 |
----------------------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
---------------------------------------------------
 4 - filter((COL1='T' AND INTERNAL_FUNCTION(COL2)))
 5 - access(COL3>=TIMESTAMP' 2014-09-25 00:00:00' AND COL3 <=TIMESTAMP' 2014-09-26 00:00:00')

Don’t be disturbed by this internal_function which pops up because of a particular use of an ‘or’ clause in the current query (this is not the goal of this article). However, spot the number of partitions scanned by Oracle when visiting the index local_n_prefix_index: 367 partitions. The whole partitions have been scanned. The Real time SQL monitoring of the corresponding sql_id shows also that 80% of the wait activity was due to the operation with id n° 4 above.

This is exactly the same issue as the last year one. Isn’t it?

I hurried up to the developer desk and got the following Q&A with him

Me: do you remember our last year discussion about non-prefixed index used to drive a query which is not eliminating partitions?

Developer:  Of course I remember and this is why the query is using the partition key and the index is a locally prefixed (contains the partition key)

Me: So where does this partition list all operation is coming from?

A careful look at the table and index definition reveal that  the table is indeed partitioned but this time the developer decided to use a virtual column (derived from col3) as the partition key. He also managed to create a local prefixed (at least he was correctly thinking that this is a prefixed) index using col3 column.

At this stage of the investigation I remembered that Jonathan Lewis has blogged about partitioning using virtual column where I have already presented (see comment n°3) a similar case to what I have been, coincidentally, asked to look at the last week.

The positive answer in the Jonathan Lewis article to the following question

Quiz: if you create a virtual column as trunc(date_col,’W’) and partition on it – will a query on date_col result in partition elimination?

Is of course valid for the trunc SQL function, but it seems, however, not extensible to other SQL functions.

It’s time now to the set up the model. I will create a simple table list partitioned by a virtual column having 365 partitions (one partition per day). Due to the high number of partitions, I will create this table using dynamic SQL

declare
       v_sql_statment   varchar2(32000);
begin
   v_sql_statment := 'create table mho_log_entry (ln_id  number primary key,ln_date date not null,ln_type_code  number not null,';
   v_sql_statment := v_sql_statment||'ln_event_typ varchar2(32 char) not null,day_in_year number(3,0) generated always';
   v_sql_statment := v_sql_statment||q'# as (to_number(to_char(ln_date,'DDD'))) virtual) partition by list (day_in_year)(#';   
   for n in 1..365 loop
     if n != 365 then
         v_sql_statment   := v_sql_statment||' partition y_dd_'||n||' values ('||n||'),';
     else
         v_sql_statment   := v_sql_statment||' partition y_dd_'||n||' values ('||n||'))';
    end if;
   end loop;     
      execute immediate v_sql_statment;
end;
/

Execute the above script and you will have the following table description

SQL> desc mho_log_entry
          Name            Null?    Type
          -------------- --------- -----------------
   1      LN_ID           NOT NULL NUMBER
   2      LN_DATE         NOT NULL DATE              -- used to derive the virtual column
   3      LN_TYPE_CODE    NOT NULL NUMBER
   4      LN_EVENT_TYP    NOT NULL VARCHAR2(32 CHAR)
   5      DAY_IN_YEAR              NUMBER(3)         -- partition key

The partition key(day_in_year) is a virtual column responding to the following formula

  to_number(to_char(ln_date,'DDD')

And the number of partitions I have generated is given by the following select

 SQL> select count(1)
    from user_tab_partitions
    where table_name = 'MHO_LOG_ENTRY';

  COUNT(1)
----------
       365

I still have to create a local “prefixed” index as the developer did to be in the exact situation as the query I have asked to tune

create index mho_ln_date on mho_log_entry (ln_date) local;

If you want to push data into this table then here is an example on how to proceed

SQL> insert into mho_log_entry
          (ln_id
          ,ln_date
          ,ln_type_code
          ,ln_event_typ
          )
     select
          *
     from
        (
       select
          rownum
         ,date '2014-01-01' + ((level - 1) * 2) dd
         ,trunc(rownum-1)
         ,case
           when mod(rownum,100) = 0 then 'I'
           when mod(rownum,10) = 0 then 'A'
           when mod (rownum,1000) = 0 then 'B'
           when mod(rownum,1e4) = 0 then 'R'
          else 'L'
          end aa
       from dual
       connect by level <=3e2
       )
     where dd <= to_date('31122014','ddmmyyyy') ;

SQL> exec dbms_stats.gather_table_stats(user, 'mho_log_entry', cascade => true);

And finally this is the query and its corresponding execution plan in 11.2.0.3.0

SQL> select *
        from
          mho_log_entry
        where
           ln_date between to_date('20082014','ddmmyyyy')
           and to_date('22082014','ddmmyyyy') ;

SQL> select * from table(dbms_xplan.display_cursor);

------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |       |       |       |
|   1 |  PARTITION LIST ALL                |               |     3 |     1 |365    |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MHO_LOG_ENTRY |     3 |     1 |365    |
|*  3 |    INDEX RANGE SCAN                | MHO_LN_DATE   |     3 |     1 |365    |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LN_DATE">=TO_DATE(' 2014-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       AND "LN_DATE"<=TO_DATE('2014-08-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

No partition elimination and 365 index range scans.

However, if I change the query to use the virtual column (day_in_year) instead of its underlying real column (ln_date), partition pruning occurs:

 SQL> select *
        from
           mho_log_entry
        where
           day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
           and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

SQL> select * from table(dbms_xplan.display_display);

-------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Pstart| Pstop |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |       |       |       |
|   1 |  PARTITION LIST ITERATOR|               |     3 |   232 |   234 |
|   2 |   TABLE ACCESS FULL     | MHO_LOG_ENTRY |     3 |   232 |   234 |
-------------------------------------------------------------------------

Only two partitions have been scanned. Note in passing the absence of the predicate part in this case.  This predicate part is absent also when using the explain plan for command:

 SQL> explain plan for
     select *
        from
           mho_log_entry
        where
           day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
           and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

SQL> select * from table(dbms_xplan.display);

-------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Pstart| Pstop |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |     3 |       |       |
|   1 |  PARTITION LIST ITERATOR|               |     3 |   232 |   234 |
|   2 |   TABLE ACCESS FULL     | MHO_LOG_ENTRY |     3 |   232 |   234 |
------------------------------------------------------------------------- 

Since Oracle is iteratively eliminating partitions it might be correct to do not expect to see the predicate part applied on the mho_log_entry table. However, see what happens when I create an index (first global and then local) on this table using the virtual column:

SQL> create index mho_day_in_year on mho_log_entry (day_in_year); 

SQL> select *
     from
      mho_log_entry
     where
       day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
       and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

-------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Pstart|Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |      |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| MHO_LOG_ENTRY   |     3 | ROWID |ROWID |
|*  2 |   INDEX RANGE SCAN                 | MHO_DAY_IN_YEAR |     3 |       |      |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DAY_IN_YEAR">=232 AND "DAY_IN_YEAR"<=234

And this is the corresponding execution plan for a local index

 drop index mho_day_in_year ;
 create index mho_day_in_year on mho_log_entry (day_in_year) local;

 select /*+ index(mho_log_entry) */ -- I don't know why I am obliged to hint the index
   *                                -- I will be back to it later
    from
      mho_log_entry
    where
       day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
       and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

-------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Pstart|Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |      |
|   1 |  PARTITION LIST ITERATOR           |                 |     3 |   232 |234   |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MHO_LOG_ENTRY   |     3 |   232 |234   |
|*  3 |    INDEX RANGE SCAN                | MHO_DAY_IN_YEAR |     1 |   232 |234   |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DAY_IN_YEAR">=232 AND "DAY_IN_YEAR"<=234)

It is interesting to note that when using an index, Oracle applied the predicate part while when full scanning the mho_log_entry table is didn’t . And both explain plan and actual execution are showing the same behaviour.

That’s said let’s be back to what motivated this article: partitioning by a virtual column cannot guaranty a partition pruning when querying the partitioned table using the physical column your partition key is based on

PS: rewriting the original query using the virtual column might not be always correct. It strongly depends on the data and on the virtual column definition. In the above designed model I have only one distinct year of data which makes the refactoring of the query possible

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: