From: Connor McDonald Subject: Re: Case Insensitivity Date: 2000/08/08 Message-ID: <398FFC78.6CB@yahoo.com>#1/1 Content-Transfer-Encoding: 7bit References: <8mnc45$oto$1@nnrp1.deja.com> To: Kevin Lang Content-Type: text/plain; charset=us-ascii X-Complaints-To: newsabuse@remarq.com X-Trace: 965762809 LGTBT6QCL22D2D501C uk21.supernews.com Organization: RemarQ http://www.remarQ.com Mime-Version: 1.0 Reply-To: connor_mcdonald@yahoo.com Newsgroups: comp.databases.oracle.server,comp.databases.oracle,comp.databases.oracle.misc Kevin Lang wrote: > > 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 There is an article on my web site which discusses what you need to enable function based indexes.. HTH -- =========================================== Connor McDonald http://www.oracledba.co.uk We are born naked, wet and hungry...then things get worse