Re: Function based index gets ignored.

Morten,

I'm getting the same results with my attempt to use function based indexes. One thing though. The Oracle docs say that you must ensure that the value of the function is not null in subsequent queries. In your case:

SELECT * FROM test_table
WHERE LOWER(key) IS NOT NULL
AND LOWER(key) = 'aaaa';

I can understand what the other responders are saying, but in my case the table I'm trying function based indexes on has 1.8 million rows and doing the equivalent of:

SELECT * FROM test_table
WHERE UPPER(key) IS NOT NULL
AND UPPER(key) = UPPER('aaaa');

(my index was created with upper) still gets me a full table scan.

Any ideas would sure be appreciated.

Thanks,

    Jim Harrington

"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 Fri Sep 21 2001 - 15:49:32 CDT