Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Indexes ignored

Re: Indexes ignored

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sat, 13 Jul 2002 08:57:47 -0400
Message-ID: <3D3023CB.B1CDF982@firstdbasource.com>


Jim Poe wrote:
>
> > Do you have a reason for using the upper function on a numeric (?) NBR
> > column ?
> >
> NBR is a varchar2 column
>
> > Do you have an index on the NBR column ? (else main query has no way of
> > reading just the required NBR)
> >
> No, but I should.
>

Oracle will only use an index if they columns being searched for are in the WHERE clause. Without an index on NBR, this query will NEVER use any index because it does not know to use any other index. This particular query will result in a full table scan.

So, add the index, analyze the tables and this one should now complete in 1-2 seconds.

Just a nit, but why would anyone use a varchar field for a field that will only contain numbers? I can't think of anyplace that uses BR549 any more... :) << that should date some people :)

-- 
Regards,

Michael Austin            OpenVMS User since June 1984
First DBA Source, Inc.    Registered Linux User #261163
Sr. Consultant            http://www.firstdbasource.com
                          http://www.firstdbasource.com/donation.html
704-947-1089 (Office)     704-236-4377 (Mobile)
Received on Sat Jul 13 2002 - 07:57:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US