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

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,
-- 
FREUNDLICHE GRüßE

I.A.
MARTIN KLIER
SYSTEMADMINISTRATION/DATENBANKEN
------------------------------------------------------------
------
A.T.U AUTO-TEILE-UNGER
HANDELS GMBH & CO. KG
DR.-KILIAN-STR. 4 
92637 WEIDEN I.D. OPF.

TEL.: +49 961 306-5663
FAX : +49 961 306-5982

MARTIN.KLIERATU.DE
WWW.ATU.EU

SITZ: WEIDEN I. D. OPF., AMTSGERICHT WEIDEN I. D. OPF., HRA
2012
UST-ID NR. DE814195392, WEEE-NR. DE53789710
PERSöNLICH HAFTENDE GESELLSCHAFTERIN:
AFM AUTOFAHRERFACHMARKT GESCHäFTSFüHRUNGS GMBH
SITZ: WEIDEN I. D. OPF., AMTSGERICHT WEIDEN I. D. OPF., HRB
2842
GESCHäFTSFüHRER: KARSTEN ENGEL, DIRK MüLLER, MANFRED RIES
------------------------------------------------------------
------

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: suse-oracle-unsubscribesuse.com
For additional commands, email: suse-oracle-helpsuse.com
Please see http://www.suse.com/oracl
e/ before posting