function-based-index doesn' work

From: Tisan Gabriel <de_at_dorner.at>
Date: 2000/03/15
Message-ID: <8anmri$4sr$1@pollux.ip-plus.net>#1/1

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 Wed Mar 15 2000 - 00:00:00 CST