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).
