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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie: Query optimization

Re: Newbie: Query optimization

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Tue, 4 Jun 2002 14:47:23 +0100
Message-ID: <3cfcc4ee$0$8505$cc9e4d1f@news.dial.pipex.com>


"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 UK



> 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)
Received on Tue Jun 04 2002 - 08:47:23 CDT

Original text of this message

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