Mohamed Houri’s Oracle Notes

October 24, 2020

Function based indexes and cursor sharing

Filed under: Oracle — hourim @ 1:20 pm

To be selected by Oracle, a Function Based Index (FBI) must have a definition that matches the predicate expression of the query. For example, the following FBI index is used because its definition matches perfectly the predicate part:

SQL> create index fbi_ind on t1 (case when mod(id,100) =0 then 1 else -1 end);

Index created.

SQL> select
         count(1) 
     from t1 
     where 
          (case when mod(id,100) =0 then 1 else -1 end) = 1;

  COUNT(1)
----------
     11010

SQL> @xpsimp

SQL_ID  b637sp3wfptm4, child number 0
-------------------------------------
-------------------------------------------------
| Id  | Operation             | Name    | Rows  |
-------------------------------------------------
|   0 | SELECT STATEMENT      |         |       |
|   1 |  SORT AGGREGATE       |         |     1 |
|*  2 |   INDEX FAST FULL SCAN| FBI_IND | 11010 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."SYS_NC00005$"=1)

However, if I slightly change the predicate part of the above query, then Oracle will cease to use that index

SQL> select count(1) 
     from t1 
        where (case when mod(id,100) =0 then 9 else -1 end) = 1;

  COUNT(1)
----------
         0
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| T1   | 11010 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(CASE MOD("ID",100) WHEN 0 THEN 9 ELSE (-1) END =1)

Again I have to create a matching index if I want Oracle to use an index access for the above query

SQL> create index fbi_ind_2 on t1 (case when mod(id,100) =0 then 9 else -1 end);

SQL> select count(1) 
     from t1 
        where (case when mod(id,100) =0 then 9 else -1 end) = 1;

  COUNT(1)
----------
         0
---------------------------------------------------
| Id  | Operation             | Name      | Rows  |
---------------------------------------------------
|   0 | SELECT STATEMENT      |           |       |
|   1 |  SORT AGGREGATE       |           |     1 |
|*  2 |   INDEX FAST FULL SCAN| FBI_IND_2 | 11010 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."SYS_NC00006$"=1)

Let’s now force the cursor sharing parameter value and repeat the experiment

SQL> alter session set cursor_sharing=force;

select
        count(1)
    from t1
    where
         (case when mod(id,100) =0 then 1 else -1 end) = 1;

  COUNT(1)
----------
     11010

SQL_ID  31tfkvvc1k4hf, child number 0
-------------------------------------
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| T1   | 11010 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(CASE MOD("ID",:SYS_B_1) WHEN :SYS_B_2 THEN :SYS_B_3 ELSE
              (-:SYS_B_4) END =:SYS_B_5)

The FBI index is not anymore used. Do you know why?

This is simply, because, as stated above, the definition of the FBI_IND function-based index is not anymore matching the definition of the predicate part n°2 because the forced cursor sharing value has transformed the literal values in the predicate part to a system bind variable values: SYS_B_1, SYS_B_2 etc..

This is exactly what occurred to my client using third party software. To remedy this situation I had to create a SQL patch to force an exact value of the cursor sharing parameter but only for this particular query:

SQL> DECLARE
  2     patch_name varchar2(100);
  3  BEGIN
  4     patch_name := sys.dbms_sqldiag.create_sql_patch(
  5                  sql_id    => '31tfkvvc1k4hf',
  6                  hint_text => 'cursor_sharing_exact',
  7                  name      => '31tfkvvc1k4hf_cursor_sharing_exact');
  8  END;
  9  /

PL/SQL procedure successfully completed.
select
        count(1)
    from t1
    where
         (case when mod(id,100) =0 then 1 else -1 end) = 1;

  COUNT(1)
----------
     11010

SQL> @xpsimp

SQL_ID  ft9dsb7qj8yfr, child number 0
-------------------------------------
select         count(1)     from t1     where          (case when
mod(id,100) =0 then 1 else -1 end) = 1

-------------------------------------------------
| Id  | Operation             | Name    | Rows  |
-------------------------------------------------
|   0 | SELECT STATEMENT      |         |       |
|   1 |  SORT AGGREGATE       |         |     1 |
|*  2 |   INDEX FAST FULL SCAN| FBI_IND | 11010 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."SYS_NC00005$"=1)

Note
-----
   - SQL patch "31tfkvvc1k4hf_cursor_sharing_exact" used for this statement

I know that you’ve already pointed out that the FBI index is now used by Oracle, but did you point out that the SQL_ID has changed (ft9dsb7qj8yfr)?

This is the effect of the applied SQL patch which imposes the usage of the exact value of the cursor sharing parameter. So, before hurrying up and trying to make your critical query using the FBI index, you have to measure whether using literal values will not turn your execution pain into a parsing penalty.

While I was troubleshooting a similar issue for the same third party software something attracted my attention. I created a function based index using a SUBSTR function and was in my way of creating a similar patch to force the exact value of the cursor sharing when I remarked that my new FBI index is already used

Here’s a model of my client case:

create table t1
    as
        select
            rownum               n1,
            'xy' || rownum         vc1,
            mod(rownum, 10)       n2
        from
            dual
        connect by
            level <= 1e4;

create index idx_t1 on t1(substr(vc1,3,1));

alter session set cursor_sharing=force;

select * from t1 where substr(vc1,3,1)='5';

SQL> @xpout

SQL_ID  djypd1v8qjawh, child number 0
-------------------------------------
select * from t1 where substr(vc1,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

--------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |   100 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T1 |    40 |
-------------------------------------------------------------
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" "IDX_T1")
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("T1"."SYS_NC00004$"=:SYS_B_2)

My client was upgrading from a 12.2 to a 19c and I was practically sure that in the source database release the FBI index was not used under force cursor sharing. This is why I altered the optimizer_features_enabled (OFE) parameter in the destination database to the previous version and launched again the same query


SQL> alter session set optimizer_features_enable='12.2.0.1';

Session altered.

SQL> select * from t1 where substr(vc1,3,1)='5';

SQL_ID  djypd1v8qjawh, child number 1
-------------------------------------
select * from t1 where substr(vc1,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013
------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |       |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |
------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("VC1",:SYS_B_0,:SYS_B_1)=:SYS_B_2)

I have then checked different values of the OFE until I realized that this change occurred as from 18.1


SQL> alter session set optimizer_features_enable='18.1.0';

SQL> select * from t1 where substr(vc1,3,1)='5';

Plan hash value: 3491035275
--------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |   100 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T1 |    40 |
--------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" "IDX_T1")
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("T1"."SYS_NC00004$"=:SYS_B_2)

Thanks to Ivica Arsov (@IvicaArsov) I know now that this was addressed by Oracle in the following bug

Patch 25575369: FUNCTION BASED INDEX BASED WITH SUBSTR NOT USED WHEN CURSOR_SHARING=FORCE

It’s a good improvement done by Oracle to allow FBI indexes that contain a SUBSTR function in their definition, to be used by Oracle under forced cursor sharing value.

And it looks also that the LPAD function is also not anymore a FBI pre-empting reason(under forced cursor sharing) as the following example proves:


create index idx2_t1 on t1(lpad(vc1,2,'z'));

select * from t1 where lpad(vc1,2,'z') = 'xy';

SQL_ID  g9zvaj3sq5ptm, child number 0
-------------------------------------
select * from t1 where lpad(vc1,:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2"

Plan hash value: 2927120560
---------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1      |   100 |
|*  2 |   INDEX RANGE SCAN                  | IDX2_T1 |    40 |
---------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" "IDX2_T1")
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."SYS_NC00005$"=:SYS_B_2)

Bottom Line

In your Oracle learning process, I think it is always a good idea to unlearn some evidence; particularly when you go from an older to a newer release. In this article, I demonstrated that, as from 18c and on, certain function-based indexes can be used by Oracle under forced cursor sharing value.

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.

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)