Re: please help with stored outlines

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<3eea133f$0$19598$cc9e4d1f_at_news.dial.pipex.com>...
> "Ryan" <rgaffuri_at_cox.net> wrote in message
> news:577Ga.12036$Id7.10856_at_news2.east.cox.net...
> > However its using different copies of the same tables. The tables have the
> > same names, they are just owned by a different user and stored in a
> > different tablespace. is it possible to use the outline for this purpose?
>
> I can't see how this would be possible, (doesn't mean it isn't). Suppose
> User B has objects of a different type with the same name, it would be
> somewhat daft for Oracle to try to use the same (invalid) execution plan.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************

Parsing, at least soft parsing still needs to take place, of course. If there is any table structural or access privilege problem, ORACLE will let you know about it. This does not need to happen in a different schema. It can happen in the same schema as well between the time you generate the outlines and the time you want to use them.

A couple of other similar issues are:
* What if the original plan uses an index range scan, but the index has been lost by the time you want to use the outlines? Well, ORACLE still looks up the outline table and finds a match. ORACLE tries to use the plan by way of hints stored in the outline tables. Index access is not possible, hints are ignored and a different access path is chosen.

• What if the original plan uses full table scan, but an index has been introduced after oultines have been created and before you want to use them? An index scan will be faster, but since you ask ORACLE to use outlines and the plan from outlines is valid, ORACLE will use the sotred plan and will not try to use the index.

As far as I can see, this is how ORACLE handles the outlines is these situations.

• Jusung Yang