Re: Index not Being Used

Hi.

 The reason optimizer is NOT using indexes is the usage of function  ( UPPER ) on index field(s).
 If you are workink with Oracle 8i - consider using function based  indexes otherwise define another column in your table,  populate it with trigger as Upper ( last_name ) and perform searches  over that column.

 HTH. Michael.

In article <8809f8$kc2$1_at_nnrp1.deja.com>,   grey1969_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 11 2000 - 12:30:59 CST