Case Insensitivity

From: Kevin Lang <KLang_at_kevinlang.com>
Date: 2000/08/08
Message-ID: <DjQj5.71717$3E6.720890@news1.alsv1.occa.home.com>#1/1

Hello,

How is case insensitivity emulated in Oracle? I see that there are function-based indexes that would allow me to index a mixed-case column as UPPER(colname), and then use the "UPPER" function in the predicate, but I can not get the optimizer to use that index. A table scan is always generated. I have analyzed the table and indexes updating the statistics, there are no NULLS, all of the rules are followed.

CREATE TABLE TABLE1 (COL1 number, COL2 varchar2(50) ) CREATE INDEX INDEX1 on TABLE1 UPPER(COL2) -- syntax off the top of my head - may not be perfect.

ANALYZE TABLE1 COMPUTE STATISTICS                 ""

""
EXPLAIN PLAN SET STATEMENT_ID = '1' for Select COL1 from TABLE1 where UPPER(COL2) = UPPER :VARIABLE1 There are over 100,000 rows in the table, and a variety of syntactical variations of the predicate have been tried, but the index is never selected.

Is there anybody else implementing ad-hoc searching of mixed-case character columns?

Thank You,

Kevin Lang
KLang_at_kore.com Received on Tue Aug 08 2000 - 00:00:00 CDT