Jerry Gitomer wrote:
>
> Modify the queries so that they convert the name to the format used in the
> index.
>
> regards
>
> Jerry Gitomer
> -----------------------------------------------------------
> ebektech wrote in message ...
> >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
> >
> >
Or, you can do something like this:
CREATE INDEX AIX_CUSTOMER_NAME ON CUSTOMER(UPPER(NAME));
You should set QUERY_REWRITE_ENABLED to true in any session
that wants to make use of the index created with the statement above
by executing the corresponding alter session command:
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
The combination of the two SQL statements above will make it
possible for Oracle to use index on queries that contain a condition
like WHERE UPPER(NAME) = 'SUCKER';
--
Mladen Gogala
Received on Sun May 16 1999 - 16:35:55 CDT
