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

[steven]

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
--------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 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

[Jonathan Lewis]

"steven" <zhan...@sssltd.cn> wrote in message
news:1137372270.8...@g14g2000cwa.googlegroups.com...

Sometimes the answer is simply that the code
hasn't been written yet. Possibly in this case
there are some subtle details to worry about
with functions that could return a null and the
use of the lnnvl() function.

Interesting point, though, 9.2.0.6 can manage
to use both indexes if it has (the default) value
TRUE for _b_tree_bitmap_plans. Something
like:

select * from t1 where n1 = 1 or n2+1 = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=30 Bytes=5670)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=9 Card=30
Bytes=5670)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'T_IF' (NON-UNIQUE) (Cost=1)

t_if is an index on t1(n2+1)

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 15th Jan 2006

[xho...@gmail.com]

"steven" <zhan...@sssltd.cn> wrote:
> Hi,
> I see the function-based index restrictions from otn
> link,
> http://www.oracle.com/pls/db92/print_hit_summary?search_string=Restrictio
> ns+for+Function-Based+Indexes
>
> Function-based indexes are not used when OR-expansion is done.

....

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

> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1990 | 2017K| 63 |

> 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;

> | Id | Operation | Name | Rows | Bytes | Cost |

> -------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 2000 | 2027K| 306 |

Even if Oracle could use function-based index in an OR expansion, it would
probably choose not to do so in this case, as the estimated cost of the
FTS seems to be much lower than for the OR-expanded-like execution plan.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

[steven]

I see. Thank you very much.

Thanks,
Steven