Re: SELECT Output Default Ordering ?

Thanks, Cary.

Should have mentioned that the CBO has not always known _not_to_sort_ when an index-access path is available - Oracle7/8.0 in particular.

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

> This is not so much a response to Binley's comment as a general note on
the
> whole thread.
>
> Not using ORDER BY when you need it is a huge risk... It doesn't matter
what
> hint you have, if someone drops the index, you're hosed. That's what the
> ORDER BY is for. If you use the ORDER BY clause with the right index, then
> performance will be spectacular. The Oracle query optimizers are smart
> enough not to sort if the rows are coming from an index guaranteed to
> provide the specified ordering.
>
> If the index is missing, then of course performance will blow, but at
least
> the application will produce the correct answer. The users screaming about
> the performance problem will clue you in that you have to fix something.
But
> at least the users won't ever get the wrong answer back from the
> application.
>
> The huge problem with using the index hint without the ORDER BY clause is
> that situations can easily occur in which users might go on for *months*
> getting wrong data out of the database without ever knowing there's a
> functional bug in their application.
>
>
> 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-----
> Binley Lim
> Sent: Wednesday, June 25, 2003 6:51 PM
> To: Multiple recipients of list ORACLE-L
>
> 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).
>
> --
> 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).