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.
Mohamed Houri’s Oracle Notes 