Re: ordered_hint

Walt wrote:
> Chuck wrote:
>
>
> > If you have a query that joins table t1 and view v1, and the query uses
> > an ORDERED hint, exactly what order will the optimizer join the tables
> > in? Will it use t1 as the driving table and join the tables in v1 in the
> > same order they're listed in the view's FROM clause? Or will the order
> > they're listed in the view be irrelevant? I was looking for
> > documentation on this but didn't find any. The Performance Tuning Guide
> > only deals with the scenario where everything in the FROM clause is a table.
> >
> > example:
> >
> > select --+ordered
> > t1.cola, v1.colb
> > from t1, v1
> > where t1.colx = v1.coly
> >
> > Oracle version in question is 9.2.0.5.
>
> My understanding is that views are basically a convienece for the user
> and that the SQL parser replaces the view with the base tables before
> proceeding any farther. Somewhat akin to preprocessing #include
> directives in C before compiling.
>
> For instance, if your view was
>
> Creat view v1
> CREATE OR REPLACE VIEW v1
> AS select t2.colb, t2.coly
> from t2, t3
> where t2.id=t3.id
>
> Your query will be parsed as if you actually submitted:
>
> select --+ordered
> t1.cola, t2.colb
> from t1,t2,t3
> where t1.colx = v2.coly
> and t2.id=t3.id
>
> Any statistics or indicies on the base tables will be used to generate
> the execution plan for the query.
>
> In this case, the ordered hint *should* cause the optimizer to join the
> tables in the order that they are listed in the view. DISCLAIMER: I
> haven't tested it.
>
> //Walt

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/hintsref.htm#PFGRF50104

The outcome depends on whether the view can be merged, among other things. Received on Wed Sep 21 2005 - 17:08:26 CDT