Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Function based index gets ignored.

Function based index gets ignored.

From: Morten Primdahl <morten_at_caput.com>
Date: Thu, 20 Sep 2001 16:49:38 +0200
Message-ID: <3BAA0202.3070607@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 - 09:49:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US