Pierre -- Thanks for the question regarding "function based index not used ", version 10.2.0
Submitted on 19-Feb-2008 5:29 Central time zone
Last updated 20-Feb-2008 8:10
You Asked
I would like to understand why a function based index is not used in the following test case where I would like to index only a subset of the table data:
Oracle version is 10.2.0.2 on Windows (or 10.2.0.1 on Linux).
bas002> set linesize 132
bas002>
bas002> drop table t;
Table dropped.
bas002>
bas002> create table t (c1 varchar2(100), c2 varchar2(100), c3 varchar2(100), c4
varchar2(100));
Table created.
bas002>
bas002> create index i on t(
2 -- c1 || c2
3 case when (c1||c2 = 'VAL1VAL3') then c1 || c2
4 when (c1||c2 = 'VAL1VAL4') then c1 || c2
5 when (c1||c2 = 'VAL2VAL3') then c1 || c2
6 when (c1||c2 = 'VAL2VAL4') then c1 || c2
7 else null
8 end
9 );
Index created.
bas002>
bas002> insert into t
2 select dbms_random.value, dbms_random.value, dbms_random.value, dbms_random.value
3 from dual connect by level < 100000;
99999 rows created.
bas002> insert into t values('VAL1','VAL3','VAL5','VAL7');
1 row created.
bas002> commit;
Commit complete.
bas002>
bas002> exec dbms_stats.gather_table_stats(ownname => 'O', tabname => 'T', cascade =>
TRUE);
PL/SQL procedure successfully completed.
bas002>
bas002> set autotrace on
bas002> select * from t where c1 || c2 = 'VAL1VAL3';
C1
------------------------------------------------------------------------------------------
----------
C2
------------------------------------------------------------------------------------------
----------
C3
------------------------------------------------------------------------------------------
----------
C4
------------------------------------------------------------------------------------------
----------
VAL1
VAL3
VAL5
VAL7
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 156K| 517 (2)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 1000 | 156K| 517 (2)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"||"C2"='VAL1VAL3')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2330 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Without case it works but we have to index everyting:
bas002>
bas002>
bas002> set autotrace off
bas002> drop table t;
Table dropped.
bas002>
bas002> create table t (c1 varchar2(100), c2 varchar2(100), c3 varchar2(100), c4
varchar2(100));
Table created.
bas002>
bas002> create index i on t( c1 || c2);
Index created.
bas002>
bas002>
bas002> insert into t
2 select dbms_random.value, dbms_random.value, dbms_random.value, dbms_random.value
3 from dual connect by level < 100000;
99999 rows created.
bas002> insert into t values('VAL1','VAL3','VAL5','VAL7');
1 row created.
bas002> commit;
Commit complete.
bas002>
bas002> exec dbms_stats.gather_table_stats(ownname => 'O', tabname => 'T', cascade =>
TRUE);
PL/SQL procedure successfully completed.
bas002>
bas002>
bas002> set autotrace on
bas002> select * from t where c1 || c2 = 'VAL1VAL3';
C1
------------------------------------------------------------------------------------------
----------
C2
------------------------------------------------------------------------------------------
----------
C3
------------------------------------------------------------------------------------------
----------
C4
------------------------------------------------------------------------------------------
----------
VAL1
VAL3
VAL5
VAL7
Execution Plan
----------------------------------------------------------
Plan hash value: 242607798
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 160 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 160 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"||"C2"='VAL1VAL3')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
bas002> exit
Thank you.
and we said...
simple.
You put a predicate on c1||c2:
bas002> select * from t where c1 || c2 = 'VAL1VAL3';
the function is "c1||c2"
You indexed:
bas002> create index i on t(
2 -- c1 || c2
3 case when (c1||c2 = 'VAL1VAL3') then c1 || c2
4 when (c1||c2 = 'VAL1VAL4') then c1 || c2
5 when (c1||c2 = 'VAL2VAL3') then c1 || c2
6 when (c1||c2 = 'VAL2VAL4') then c1 || c2
7 else null
8 end
9 );
that is not c1||c2, that is "case when .... end"
I do not understand why you even think it could be used?
what you "where" on is so different from what you indexed??
you either
a) index c1||c2 - which you did and saw it worked
b) "where" on the case statement.
ops$tkyte%ORA10GR2> create index i on t(
2 case when (c1||c2 = 'VAL1VAL3') then c1 || c2
3 when (c1||c2 = 'VAL1VAL4') then c1 || c2
4 when (c1||c2 = 'VAL2VAL3') then c1 || c2
5 when (c1||c2 = 'VAL2VAL4') then c1 || c2
6 else null
7 end
8 );
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values('VAL1','VAL3','VAL5','VAL7');
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats(user, 'T', numrows=>100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a90 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t
2 where case when (c1||c2 = 'VAL1VAL3') then c1 || c2
3 when (c1||c2 = 'VAL1VAL4') then c1 || c2
4 when (c1||c2 = 'VAL2VAL3') then c1 || c2
5 when (c1||c2 = 'VAL2VAL4') then c1 || c2
6 else null
7 end = 'VAL1VAL3';
Execution Plan
----------------------------------------------------------
Plan hash value: 242607798
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 97K| 5 (0)| 00:00
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1000 | 97K| 5 (0)| 00:00
|* 2 | INDEX RANGE SCAN | I | 400 | | 1 (0)| 00:00
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "C1"||"C2" WHEN 'VAL1VAL3' THEN "C1"||"C2" WHEN
'VAL1VAL4' THEN "C1"||"C2" WHEN 'VAL2VAL3' THEN "C1"||"C2" WHEN '
THEN "C1"||"C2" ELSE NULL END ='VAL1VAL3')
ops$tkyte%ORA10GR2> @atoff
ops$tkyte%ORA10GR2> set autotrace off
I think I understand better
February 19, 2008 - 3pm Central time zone
Reviewer: Pierre from France
Thanks again. This is now a better example:
SQL>
SQL> whenever sqlerror exit failure;
SQL>
SQL> create table t (c1 varchar2(100), c2 varchar2(100), c3 varchar2(100), c4 varchar2(100));
Table creee.
SQL>
SQL> create or replace function fi (n1 varchar2, n2 varchar2)
2 return varchar2
3 deterministic
4 is
5 begin
6 if (n1||n2 = 'VAL1VAL3') then return n1||n2 ;
7 elsif (n1||n2 = 'VAL1VAL4') then return n1||n2 ;
8 elsif (n1||n2 = 'VAL2VAL3') then return n1||n2 ;
9 elsif (n1||n2 = 'VAL2VAL4') then return n1||n2 ;
10 else return null;
11 end if;
12 end;
13 /
Fonction creee.
SQL> show errors
Pas d'erreur.
SQL>
SQL> create index i on t(fi(c1,c2));
Index cree.
SQL> insert into t values('VAL1','VAL3','VAL5','VAL7');
1 ligne creee.
SQL> commit;
Validation effectuee.
SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade => TRUE);
Procedure PL/SQL terminee avec succes.
SQL>
SQL> set autotrace on
SQL> select * from t where fi(c1,c2) is not null;
C1
----------------------------------------------------------------------------------------------------
C2
----------------------------------------------------------------------------------------------------
C3
----------------------------------------------------------------------------------------------------
C4
----------------------------------------------------------------------------------------------------
VAL1
VAL3
VAL5
VAL7
Plan d'execution
----------------------------------------------------------
Plan hash value: 313748384
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OPS$PIERRE"."FI"("C1","C2") IS NOT NULL)
Statistiques
----------------------------------------------------------
21 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
http://forums.oracle.com/forums/message.jspa?messageID=2353857 Followup February 20, 2008 - 8am Central time zone:
no, it would be a way to make things slower - but I see no advantage to it.
I already showed how to do this right above.
If you do not like putting the CASE into the query, just use a view
ops$tkyte%ORA10GR2> create table t (c1 varchar2(100), c2 varchar2(100), c3 varchar2(100), c4
varchar2(100));
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index i on t(
2 case when (c1||c2 = 'VAL1VAL3') then c1 || c2
3 when (c1||c2 = 'VAL1VAL4') then c1 || c2
4 when (c1||c2 = 'VAL2VAL3') then c1 || c2
5 when (c1||c2 = 'VAL2VAL4') then c1 || c2
6 else null
7 end
8 );
Index created.
ops$tkyte%ORA10GR2> create or replace view v
2 as
3 select c1, c2, c3, c4,
4 case when (c1||c2 = 'VAL1VAL3') then c1 || c2
5 when (c1||c2 = 'VAL1VAL4') then c1 || c2
6 when (c1||c2 = 'VAL2VAL3') then c1 || c2
7 when (c1||c2 = 'VAL2VAL4') then c1 || c2
8 else null
9 end c1_c2
10 from t
11 /
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats(user, 'T', numrows=>100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a90 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t
2 where case when (c1||c2 = 'VAL1VAL3') then c1 || c2
3 when (c1||c2 = 'VAL1VAL4') then c1 || c2
4 when (c1||c2 = 'VAL2VAL3') then c1 || c2
5 when (c1||c2 = 'VAL2VAL4') then c1 || c2
6 else null
7 end = 'VAL1VAL3';
Execution Plan
----------------------------------------------------------
Plan hash value: 242607798
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 97K| 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1000 | 97K| 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 400 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "C1"||"C2" WHEN 'VAL1VAL3' THEN "C1"||"C2" WHEN
'VAL1VAL4' THEN "C1"||"C2" WHEN 'VAL2VAL3' THEN "C1"||"C2" WHEN 'VAL2VAL4'
THEN "C1"||"C2" ELSE NULL END ='VAL1VAL3')
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v
2 where c1_c2 = 'VAL1VAL3';
Execution Plan
----------------------------------------------------------
Plan hash value: 242607798
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 97K| 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1000 | 97K| 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 400 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "C1"||"C2" WHEN 'VAL1VAL3' THEN "C1"||"C2" WHEN
'VAL1VAL4' THEN "C1"||"C2" WHEN 'VAL2VAL3' THEN "C1"||"C2" WHEN 'VAL2VAL4'
THEN "C1"||"C2" ELSE NULL END ='VAL1VAL3')
ops$tkyte%ORA10GR2> @atoff
ops$tkyte%ORA10GR2> set autotrace off