Re: function-based-index doesn' work

Paulo Conte pconte_at_sti.com.br

 tried to use the function-based-index in a version of Oracle 8i Server, however he informs that the characteristic is not available, somebody can confirm that is an exclusive characteristic of Oracle Enterprise Edition

tkts
Tisan Gabriel <de_at_dorner.at> wrote in message news:8anmri$4sr$1_at_pollux.ip-plus.net...
> 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 ?
>
>
Received on Thu Mar 16 2000 - 00:00:00 CST