Re: Newbie: Query optimization

Thanks, I'll give that hint a try. The data _was_ the same at the point where I was seeing if I could get the plans the same. When I ran the explain plans this morning there was probably some difference.

Thanks, and happy holiday.

Jon
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3cfcc4ee$0$8505$cc9e4d1f_at_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 - 12:39:33 CDT