Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie: Query optimization
"Jon Waterhouse" <jonwaterhouse_at_gov.nf.ca> wrote in message
news:3cfcb43d_at_news.mhogaming.com...
> I'm completely new to optimization. The SELECT statement below (which is
> actually part of a cursor definition), is run completely differently on 9i
> (which I have on my own computer for testing purposes), and 8i, which is
> where the real data is. Table definitions _and data_ are the same. Is
there
> a hint I can add that will get 8i to choose the 9i plan (which looks more
> efficient and seems to run faster)?
> Thanks,
You could try
/*+ Index ( f ix_file_compstart) */
to get it to pick up the index. (nb syntax maybe a bit off as its a holiday in the uk and I've not got oracle fired up. )
However you say that the data is the same yet your explain plans show different amounts of data returned for the select statement. (34k for 9i and 4k for 8i). is the data *really* the same. The other thing you should do is to make sure the schema has been analysed.
Finally you should not necessarily (though I don't think it applies here) expect 9i and 8i to perform in the same way even with the same data.
HTH
-- Niall Litchfield Oracle DBA Audit Commission UKReceived on Tue Jun 04 2002 - 08:47:23 CDT
> Chosen by 9i:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=751 Card=391 Bytes=34017)
> Chosen by 8i:
> SELECT STATEMENT Optimizer=CHOOSE (Cost=13601 Card=56 Bytes=4704)