Re: SELECT Output Default Ordering ?

In cases where the SQL is an important (ie frequently used) part of the application, you do get significant gains with the index hint technique.

The ORDER BY is a guarantee - you have to pay your insurance premiums (ie additional sort).

The index hint is a trade-off with the devil - you have to understand what you are doing, or it may take a few years off your life.

• Original Message ----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, June 26, 2003 9:27 AM

> Beware, though, that without explicit ORDER BY clauses, you're not
> guaranteed to get the results in the order you expect (I think you
mentioned
> this yourself, for example, when an index is missing). It's not just a
> performance problem. In some applications, you'll get the *wrong answer*
if
> you don't use ORDER BY. Example:
>
> SELECT stuff, score
> FROM tables
> WHERE conditions AND ROWNUM<=10
> ORDER BY score DESC
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney
> - Hotsos Symposium 2004, March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> DENNIS WILLIAMS
> Sent: Wednesday, June 25, 2003 3:58 PM
> To: Multiple recipients of list ORACLE-L
>
> Tanel
> No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and
don't
> hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to
make
> it sound like they must have a DBA or the customer will probably buy MS
SQL.
> So as a vendor you must have your application run reasonably well with no
> tuning if you want to sell to small sites. Otherwise the customer will
blame
> you for a fussy application. If you add an ORDER BY, sometimes Oracle will
> decide it needs to sort, or at least that is the fear. The method I
describe
> has worked pretty well over many Oracle versions (I think one of the
Oracle
> 7.1 versions ignored hints). And yes, if an index is missing, it doesn't
> blow up, it just gets really sloooooow. But it makes sense to the customer
> that if they dropped an index that things foul up. Then they don't blame
the
> vendor, just recreate the index and remember to not get so creative next
> time.
> I think the lesson here is that as a DBA you need to support certain
> applications and understand WHY the vendor did certain things a particular
> way. Right now my learning project is E.piphany so if anyone on the list
> works with that, please drop me a private note.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -----Original Message-----
> Sent: Wednesday, June 25, 2003 1:05 PM
> To: Multiple recipients of list ORACLE-L
>
>
> > Vivek
> > If you want the data returned in an order, you can create an index
with
> > the order you want, and in your query provide a hint for Oracle to use
> that
> > index. If your query is such that Oracle actually uses that index, the
> data
> > will be returned in that order. I work with a large application that
> > entirely depends on this principle. Crude but nevertheless effective.
>
> Ouch!!!
>
> Or did you mean that you still use ORDER BY, but index scan helps to avoid
> sorting?
>
> What if this index becomes invalid for some reason, your hint won't be
used,
> and if you don't have order by, your application will fail big time!
>
> Tanel.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tanel Poder
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).