Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Function based index gets ignored.
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