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