Re: function-based-index doesn' work

On Wed, 15 Mar 2000 09:42:35 +0100, "Tisan Gabriel" <de_at_dorner.at> 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
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 1000 TABLE ACCESS FULL LSBET
>

From the concepts manual:

To use a function-based index:
- The table must be analyzed after the index is created. - The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

It is the latter of these that is the problem because lsbet_nr can be null, so can mod(lsbet_nr,1000). It might work if surrounded by nvl i.e. nvl(mod(lsbet_nr,1000),1)

To be fair, this is rather a bizarre requirement to get every 1000th row. I suspect the fastest way to run the query would be to first populate a temporary table with the id's required and then to join that table to the main table e.g.

create temporary table fred as select rownum*1000 modid from lsbet where rownum <= 2000;

select lsbet.*
from lsbet, fred
where fred.modid = lsbet.lsbet_nr; Received on Wed Mar 15 2000 - 00:00:00 CST