Why function-based index cound't work with OR-expansion

From: steven <zhang.bin_at_sssltd.cn>
Date: 15 Jan 2006 16:44:30 -0800
Message-ID: <1137372270.814777.118190@g14g2000cwa.googlegroups.com>

Hi,
I see the function-based index restrictions from otn link,http://www.oracle.com/pls/db92/print_hit_summary?search_string=Restrictions+for+Function-Based+Indexes

Function-based indexes are not used when OR-expansion is done.

for example:
If use OR-expansion, the sql always does full table scan and ignore the b-tree index on instr_sedol_id = :b1 and function-based index on UPPER (instr_ric_id) .

SQL> explain plan for select * from instrument WHERE UPPER (instr_ric_id) = :b0 or instr_sedol_id = :b1;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1990 | 2017K| 63 |
|* 1 | TABLE ACCESS FULL | INSTRUMENT | 1990 | 2017K| 63 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter(UPPER("INSTRUMENT"."INSTR_RIC_ID")=:Z OR "INSTRUMENT"."INSTR_SEDOL_ID"=:Z)
Note: cpu costing is off
15 rows selected.

Then try union and it works.

SQL> explain plan for select * from instrument WHERE UPPER (instr_ric_id) = :b0 union select * from instrument WHERE instr_sedol_id = :b1;
Explained.

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 2000 | 2027K| 306 |
| 1 | SORT UNIQUE | | 2000 | 2027K| 306 |
| 2 | UNION-ALL | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| INSTRUMENT | 1000 | 1013K| 1 |

|* 4 | INDEX RANGE SCAN | INSTRUMENT_FB1 | 1000 | | 1 |

| 5 | TABLE ACCESS BY INDEX ROWID| INSTRUMENT | 1000 | 1013K| 9 |
|* 6 | INDEX RANGE SCAN | INSTRUMENT_IK12 | 1000 | | 1 | PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

4 - access(UPPER("INSTRUMENT"."INSTR_RIC_ID")=:Z) 6 - access("INSTRUMENT"."INSTR_SEDOL_ID"=:Z) Note: cpu costing is off
20 rows selected.

Is there anything difficult for Oracle kernel to implement this feather?

Thanks,
Steven Received on Sun Jan 15 2006 - 18:44:30 CST