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

"steven" <zhang.bin_at_sssltd.cn> wrote in message news:1137372270.814777.118190_at_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
> --------------------------------------------------------------------
> --------------------------------------------------------------------
> | 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
>

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