Index on a virtual column: would it help others?

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