Re: function-based-index doesn' work

Tisan Gabriel wrote:
>
> 1) I use Oracle 8i Enterprise Edition and I have the SQL statement :
>
> SELECT lsbet__kunde_nr FROM lsbet WHERE MOD(lsbet_nr,1000)=0
>
> 2) I created a function-based-index :
>
> CREATE INDEX mod_index2lsbet ON lsbet(MOD(lsbet_nr,1000));
>
> 3)I made statement
>
> ANALYZE TABLE lsbet CREATE STATISTICS
>
> 4) But Oracle doesn't use the index
>
> select lsbet__kunde_nr
> from
> lsbet where MOD(lsbet_nr,1000)=0
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 68 0.00 0.00 329907 333329 6
> 1000
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 70 0.00 0.00 329907 333329 6
> 1000
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 38
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1000 TABLE ACCESS FULL LSBET
>
> Did I make something wrong ? Why Oracle doesn't use the index scan ?

This may assist:

http://www.oracledba.co.uk/tips/function_indexes.htm

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse