Re: Function based index gets ignored.

With that small a data set, full table scan is as fast or faster than using the index... ( You let the optimizer CHOOSE, and it did )

Morten Primdahl <morten_at_caput.com> wrote:

>
>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
>

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Thu Sep 20 2001 - 11:48:20 CDT