Re: Case insensitive index scan

This is a provisional solution, but it works (well, more or less).

SELECT /*+ INDEX(customer aix_customer_name)*/ * FROM customer WHERE (name LIKE 'S%' OR name LIKE 's%') AND upper(name) = upper('SmiTH');

Aaaaaadios,

Javier Rojas

ebektech wrote:

> 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 Mon May 17 1999 - 05:37:11 CDT