Mohamed Houri’s Oracle Notes

October 25, 2013

Index on a virtual column: would it help others?

Filed under: virtual column — hourim @ 2:30 pm

Is it possible to create an index on a column a and use it for column b? In certain situations yes.

Let’s first build the model and explore those situations (in 11.2.0.3)

  create table t1
        as   select
             rownum n1
            ,to_date(to_char(to_date('01/01/1985','dd/mm/yyyy'),'J') + trunc(dbms_random.value(1,11280)),'J') ord_date
            ,dbms_random.string('L',dbms_random.value(1,30))||rownum text_comment
        from dual
        connect by level <= 10000;

  alter table t1 add virt_date date generated always as (trunc(ord_date)) virtual;

  begin
        dbms_stats.gather_table_stats
           (ownname          => user,
            tabname          =>'t1',
            method_opt       => 'for all columns size 1'
          );
      end;
     /
 

I have created a simple table t1 to which I have attached a virtual column using trunc function (here below with n°4):

  describe t1
 Name                 Type
 -------------------- --------------------
 1      N1            NUMBER
 2      ORD_DATE      DATE
 3      TEXT_COMMENT  VARCHAR2(4000 CHAR)
 4      VIRT_DATE     DATE
 

I want to explore the following select:

 select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy');
 

I have a predicate on ord_date. I have a virtual column based on ord_date. What do you think if I create an index on the virtual column?  Would it help my above query?

 create index ind_virt_date on t1(virt_date);
 select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy');

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
 |*  1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    40 |     3   (0)| 00:00:01 |
 |*  2 |   INDEX RANGE SCAN          | IND_VIRT_DATE |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("ORD_DATE"=TO_DATE(' 2011-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 2 - access("T1"."VIRT_DATE"=TRUNC(TO_DATE(' 2011-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
 

Yes it did. The CBO uses the index on the virtual column (ind_virt_date) which is based on a different column (ord_date) to cover the predicate where ord_date = to_date(’02/08/2011′,’dd/mm/yyyy’);

What I have shown up to now is: If you create an index on a virtual column, and you use a where clause on the column on which the virtual column is based on, the index is selected by the CBO.

But the question is: would this be extensible to all types of virtual columns?

And the answer is: I don’t think so

Ok, let’s test with another virtual column using again the trunc function

 alter table t1 add virt1_n1 number generated always as(trunc(n1)) virtual;
 create index ind_virt_n1 on t1 (virt1_n1);

 select * from t1 where n1 = 1;

 -------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
 |*  1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |    41 |     2   (0)| 00:00:01 |
 |*  2 |   INDEX RANGE SCAN          | IND_VIRT_N1 |    40 |       |     1   (0)| 00:00:01 |
 -------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 2 - access("T1"."VIRT_N1"=TRUNC(1))
 

The index on the virtual column has been again used to cover a predicate on its underlying column.

Let’s test another case with ceil function this time.


 drop index ind_virt_n1;
 drop index ind_virt_date;
 alter table t1 drop column virt1_n1;
 alter table t1 add virt1_n1 generated always as (ceil(n1)) virtual;

 create index ind_virt_n1 on t1(virt1_n1);

 select * from t1 where n1 = 1;

 --------------------------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
 |*  1 |  TABLE ACCESS FULL| T1   |     1 |    41 |    17   (0)| 00:00:01 |
 --------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 

The index on the virtual column has, however, not been used by the CBO in this case

Another case with abs function this time.


 alter table t1 drop column virt1_n1;
 alter table t1 add virt1_n1 generated always as (abs(n1)) virtual;
 create index ind_virt_n1 on t1(virt1_n1);

 select * from t1 where n1 = 1;

 --------------------------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
 |*  1 |  TABLE ACCESS FULL| T1   |     1 |    41 |    17   (0)| 00:00:01 |
 --------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 

Another case with nvl function this time:

alter table t1 drop column virt1_n1;
alter table t1 add virt1_n1 generated always as (nvl(n1,0)) virtual;

create index ind_virt_n1 on t1(virt1_n1);

select * from t1 where n1 = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    40 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=1)

The index on these virtual columns has not been used by the CBO.

Summary: In all cases of functions I have used to generate the virtual column, the only function that has permitted to an index created on its generated virtual column to be selected by the CBO (when this virtual column is not in the predicate part) is the trunc function.

My first thought was that when the internal definition of the virtual column matches the expression I’ve used to create the virtual column then the index will be used. But I was wrong because both trunc and ceil (or abs or nvl) have the same matching between their internal definition and the expression I have used to create them respectively:

 select column_name col_name, data_default def
 from dba_tab_columns
 where table_name = 'T1'
 and column_name in ('VIRT_DATE','VIRT_N1', 'VIRT1_N1', 'VIRT2_N1','VIRT3_N1');

 COL_NAME       DEF
 ------------  -----------------
 VIRT_DATE     TRUNC("ORD_DATE")
 VIRT_N1       TRUNC("N1",2)
 VIRT2_N1      ABS("N1")
 VIRT3_N1      NVL("N1",0)
 VIRT1_N1      CEIL("N1")
 

Do you know why the trunc function works differently here?

Ah! by the way I have also tested the trunc function by setting(in 12c) the hidden parameter _truncate_optimization_enabled from its default value TRUE to FALSE and this has not changed anything

Advertisements

1 Comment »

  1. […] enhancement probably appeared in 11.2.0.2, and I first saw it described in October 2013 in this blog note by Mohamed Houri; but 12c offers a delightful little enhancement – here’s what my table looks like in […]

    Pingback by Index Usage – 2 | Oracle Scratchpad — August 29, 2015 @ 10:34 am | Reply


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: