Re: Function based index gets ignored.

As a general rule, no index will be used if the amount of data that you want from a table is more than about 2-5% of the total amount of available data.

That's basically because full tablescans are read 8 (or more) blocks at a time, index access is block by block.

Effectively, the optimizer looked at your data and decided it would be quicker to read the entire table in one pass, and do the string manipulation in memory.

It's also the case that for this to work at all, your tables will probably need to have statistics analyzed -but I see no mention of you doing that.

Regards
HJR "Morten Primdahl" <morten_at_caput.com> wrote in message news:3BAA0202.3070607_at_caput.com...
>
> Hi. I'd like to be able to use an index in a
> case insensitive manner. Ie. the index should
> work with LOWER(col) queries.
>
> Reading Thomas Kyte's article, I tried:
>
> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
>
> CREATE TABLE test_table (key VARCHAR2(32), value VARCHAR2(32));
> CREATE INDEX test_idx ON test_table(LOWER(key));
>
> INSERT INTO test_table VALUES ('aAaa', 'bbbb');
> INSERT INTO test_table VALUES ('aaaa', 'cccc');
>
> SELECT * FROM test_table WHERE LOWER(key) = 'aaaa';
>
> KEY VALUE
> -------------------------------- --------------------------------
> aAaa bbbb
> aaaa cccc
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (FULL) OF 'TEST_TABLE'
>
>
> For some reason the index does not get used. Any ideas?
>
> Thanks,
>
> Morten
>
>
Received on Thu Sep 20 2001 - 13:30:36 CDT