Re: Oracle wont use an index and performance sucks

Is statusno defined in the table as a number or a char(2)?

If it is a char(2), the index is turned off and a table scan will be used to convert each statusno to a number for comparison with the 10.

if it is a char, try 'statusnow = to_char(10,'99')' or similar.

How do you know it is using a full table scan? Is it the same box?

In article <79ea69$m404_at_news.hk.linkage.net>,   "Rob Edgar" <robedgar_at_hkstar.com> wrote:
> Since we upgraded to 805 from 733 a month ago we have been having
> performance problems, not across every tbale but on certain specfic tables.
>
> An example is a small table of 8000 rows occupies 8mb in 4 extents with an
> index on STATUSNO, CUSTOMERPONO
>
> The follwoing statement gives an execution plan of a full table scan and
> then a sort
>
> SELECT *
> FROM XYZ
> WHERE STATUSNO = 10
> ORDER BY CUSTOMERPONO
>
> Now this takes 30 secs to execute versus less than 1 sec on 733. But why is
> it doing a full table scan when there is and index available.
>
> I have analyzed the table and the index but it makes no difference.
>
> The optimizer mode is set to CHOOSE which is the default.
>
> The server has an absolute ton of memory 512mb which is double what we had
> under 733 partly to offset the performance issue.
>
> The datbase was installed about 2 years ago and not really touched again
> until we just upgraded and during those two years the performance has been
> simply amazing without any problems but now it sucks big time in certain
> specific instances.
>
> Does anyone have some suggestions on what migth need fixing..
>
> Rob
>
>

Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Feb 05 1999 - 09:42:34 CST