Re: Enforcing plan via SQL plan baseline

From: Mihajlo Tekic <mihajlo.tekic_at_gmail.com>
Date: Fri, 3 Feb 2012 09:24:33 -0600
Message-ID: <CAGWRspbgn3iT9hzVtckBpCuOhLiN0no3TvkhJbfuGbzRXFN3Mw_at_mail.gmail.com>

Subodh,
FTS is not always bad, there are cases where it is much better access path option than an index scan.

There are many factors that determine the efficiency of the index and the index scan.

Instead going through examples and details I'll reference couple of discussions where this topic is nicely explained:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4433887271030

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:736825544526

http://www.freelists.org/post/oracle-l/Why-isnt-Oracle-Using-My-Index

There are many other available out there.

Now, some DBMS are not capable of performing multiple block reads (they perform single block reads for both, index scans and FTS), hence you may hear from people working on these database systems that index scan should be preferred over FTS.

~Mihajlo

On Fri, Feb 3, 2012 at 1:52 AM, Subodh Deshpande <deshpande.subodh_at_gmail.com
> wrote:

> please correct me if going wrong..FTS means full table scan, everyone
> recommends to avoid..you want FTS to happen in your query execution..
> query optimzer hints are generally used to optimize query..may be you can
> avoid if they are present in your query..so it will take default plan
> thanks..subodh
>
> On 3 February 2012 02:26, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
>
> > Folks,
> > When using SQL plan baseline, is it possible to instruct the optimizer
> > to use a baseline that has sub-optimal plan than the one it is currently
> > using. For example, a SQL statement, which cannot be altered because it
> > is coming out of a COTS application, is currently using an index scan
> > and I would like to force it to use FTS. I have tried forcing the
> > optimizer to use the SQL plan baseline but it keeps using the index path
> > and hence ignoring the plan baseline. Any feedback will be appreciated.
> >
> >
> >
> > Thanks
> >
> > Amir
> >
> >
> >
> >
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> =============================================
> Love me or Hate me both are in my Favour.
> Love me, I am in your Heart. Hate me, I am in your Mind.
> =============================================
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 03 2012 - 09:24:33 CST