Re: Index not Being Used

Nevermind, I believe I figured out why the index wasn't being used. The problem was it wasn't unique enough. Therefore the table scan was more efficient.

As a follow up to this question, does anyone have any suggestions for implementing a *like* search?

The program I'm working with allows a user to enter the first and/or last name of a person, and the query is performed using a like operator (select columns where last_name like 'Joe%').

The problem that I'm trying to resolve is, do I re-design how I do my searches in the program, or is there a database technique that I haven't considered. Thanks.

In article <87vda2$r8$1_at_nnrp1.deja.com>,   greyson.smith_at_convergys.com wrote:
> I'm running a query, and for some reason unknown to me, the indexes
> aren't being used. When I run the query, even though I am using
indexes
> in the search criterea, the query is scanning the entire table.
>
> The problem with the table scan is that there are over a million
records
> in the table, and it's taking a very long time to run the query.
>
> Does anyone know why an index wouldn't be used in a select, and more
> importantly, how can I tell Oracle to use the index and not scan the
> table. All help is appreciated.
>
> Here is the query that I am trying to run:
>
> SELECT CONTACT.CONTACT_ID, CONTACT.FIRST_NAME, CONTACT.LAST_NAME,
> CONTACT.BUSINESS_NAME, CONTACT.PHONE, CONTACT.ADDRESS,
> POSTAL_CODES.CITY, POSTAL_CODES.STATE,
> POSTAL_CODES.POSTAL_CODE
> From CONTACT, POSTAL_CODES
> Where CONTACT.POSTAL_ID = POSTAL_CODES.POSTAL_ID (+)
> AND CONTACT.CLIENT_ID = 1
> AND UPPER (CONTACT.FIRST_NAME) LIKE 'AL%'
> AND CONTACT.UPPER_LAST_NAME = 'SM'
> AND UPPER (CONTACT.LAST_NAME) LIKE 'SMIT%'
> ORDER BY upper (CONTACT.LAST_NAME), upper (CONTACT.FIRST_NAME)
>
> Client_ID and UPPER_LAST_NAMER comprise the index, but for some reason
> the index is not being utilized. Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 11 2000 - 00:16:10 CST