Index not Being Used

From: <greyson.smith_at_convergys.com>
Date: 2000/02/10
Message-ID: <87vda2$r8$1@nnrp1.deja.com>#1/1

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. Received on Thu Feb 10 2000 - 00:00:00 CST