Re: Indexes ignored

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sun, 14 Jul 2002 19:29:32 +1000
Message-ID: <gpbY8.34700$Hj3.104795@newsfeeds.bigpond.com>

Hi Jim,

Still confused why you are using an upper function on a numeric value (albeit in varchar2).

Note that the use of the upper function is disabling the use of the index (hence my suggestion to create a function based index).

Regards

Richard
"Jim Poe" <jbpoe_at_pacbell.net> wrote in message news:MPG.1799ea25daf4ede8989682_at_news.sf.sbcglobal.net...
> > 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.
> >
>
> My mistake, NBR has a unique index.
>
> > So, add the index, analyze the tables and this one should now complete
> > in 1-2 seconds.
> >
> Still taking 10 secs.
>
> > 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 :)
> >
> >
> Because there is no advantage to defining it as a
> number. The data will never be used as a number. Also,
> large international numbers can get you into range
> problems depending on the front end tool you are using.
>
> Thanks
>
> --
> Jim Poe
> <jbpoe_at_pacbell.net>
Received on Sun Jul 14 2002 - 04:29:32 CDT