Re: ordered_hint

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 Received on Wed Sep 21 2005 - 14:56:34 CDT