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

"steven" <zhang.bin_at_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