Case insensitive index scan

From: ebektech <ebektech_at_videotron.ca>
Date: Fri, 14 May 1999 13:48:49 -0400
Message-ID: <DkZ_2.2263$FR.43608@weber.videotron.net>

I have a simple table which contains around 50,000 rows, defined as:

CREATE TABLE CUSTOMER(NAME VARCHAR2(35)); with an index on the name column defined as: CREATE INDEX AIX_CUSTOMER_NAME
ON CUSTOMER(NAME); The customer names are stored with upper and lower case characters ie. Smith. or O'Grady. The users querying this table want to be able to search customer names
without having to specify the exact case for each of the characters in the name.
ie. select * from customer where upper(name) = upper('SmiTH');

Oracle will does a full scan of the customer table for the above statement and
the response time is not acceptable. Is there any other way other then adding another column to the table to force Oracle to use the index? Please reply to newsgroup and my mail address.

Thanks Received on Fri May 14 1999 - 12:48:49 CDT