Re: Case insensitive index scan

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Fri, 14 May 1999 12:58:49 -0700
Message-ID: <373C8079.C7E5774C@us.oracle.com>

You have two options:

1. Upgrade to 8i, if you're not on that release. It supports function-based indexes where you can do something like this:

CREATE INDEX AIX_CUSTOMER_NAME ON customer (UPPER(name));

2. Store the upper'ed (if there is such a word) value within the table. You can do this either through the accessing application forcing data entry to upper case, or by creating an additional column, populating the additional column with the upper'ed value via a trigger, then querying the additional column.

Of course, upgrading to 8i for just this functionality may be overkill!

HTH. Pete

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

--

Regards

Pete

---

Pete Sharman                              Email: psharman_at_us.oracle.com
Project Manager                           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

Oracle Corporation

SQL> select standard_disclaimer, witty_remark   2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

---

Received on Fri May 14 1999 - 14:58:49 CDT