Re: 10g/9i: ORDER BY in view vs. ORDER BY in SELECT

Martin Klier wrote:
> Hi DBA's,
>
> is there any difference in query plans if I do "order by" within a view or 
> alternatively order my data in the select _on_ the view? 
>
> My tests with 10gR2 did not show any difference in query plans with order by 
> in view vs. order by in the select on the view.
>
> Theoretically it's possible that using the "order by" in the select statement 
> is more efficient, since it will order only the limited results, not the 
> whole view result. But since the optimizer often does sophisticated things, I 
> wondered if it will optimize the contrary case? I have not been able to find 
> Oracle documentation on that special case. And if yes, how can I 
> control/proof that behaviour in 10g and/or 9i?
>
> Thanks a lot and best regards,
>   
Hi

If the view is "mergeable" the select of the view will be merged
into the enclosing statement resulting in a normal select.
In  this case the sort operation will be performed as the last operation
and only on the number of rows you'll get into final result set - no
matter the
position of the "order by".


If the view is not "mergeable" the full result set of the view will be
sorted if the "order by" (or other sorting requiring operation) is included
in the view.

You can determine what is happening by examining the execution plan:
if the execution plan contains a "VIEW" operation that passes the rows from
the table and/or index accesses defined by the view the view has not been
merged.
In that case a sort operation (like "order by") included in the view
definition
will be seen as follows:

VIEW OF ...
   SORT (ORDER BY)
       TABLE ACCESS (FULL) OF 'EMPLOYEES'

This will tell you that all the rows from 'EMPLOYEES' are retrieved (and
possibly filtered) and sorted BEFORE being sent to the surrrounding select.

If the sort operation is written in the surrounding select (and not in
the view)
the execution plan would look like this:

SORT (ORDER BY)
    VIEW OF ...
         TABLE ACCESS (FULL) OF 'EMPLOYEES'

In this case only the rows in the final result set will be sorted.

Note that the "VIEW" operation in above two examples is the key to determine
whether the view was merged or not.

A SQL trace will tell you the number of rows output from each row source
and you will therefore be able to determine the number of rows sorted by
the sort operations.

-- 
Best regards

Martin Berg

web: www.berg-consult.com


-- 
To unsubscribe, email: [email protected]
For additional commands, email: [email protected]
Please see http://www.suse.com/oracle/ before posting