Re: function-based-index doesn' work

Hi

I thnk that the reason for this is your databases optimizer mode CHOOSE, it should be FIRST_ROWS or ALL_ROWS for the cost-based optimizer. You can try HINTS :
SELECT /*+ index( lsbet mod_index2lsbet */ lsbet__kunde_nr FROM lsbet WHERE MOD(lsbet_nr,1000)=0

rgrds
Kari Pannila
Oracle DBA (OCP)

PS: Delete X to get working mail address...

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 ?

--