From: "Kevin Lang" Subject: Case Insensitivity Date: 2000/08/08 Message-ID: #1/1 References: <8mnc45$oto$1@nnrp1.deja.com> X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Complaints-To: abuse@home.net X-Trace: news1.alsv1.occa.home.com 965726563 24.21.61.164 (Tue, 08 Aug 2000 02:22:43 PDT) Organization: @Home Network X-MSMail-Priority: Normal NNTP-Posting-Date: Tue, 08 Aug 2000 02:22:43 PDT Newsgroups: comp.databases.oracle.server,comp.databases.oracle,comp.databases.oracle.misc 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@kore.com