Connor - Congratulations Stephane,
I'm in the camp of "don't put hints in when not necessary". My reasons for this include (some have been mentioned already): 1) You are limiting functionality when the database is upgraded - I have seen several examples where Oracle went from 7 to 8 and noone looks at every SQL statement to reevaluate the validity of every hint. 2) Changes in data volume or distribution can invalidate the benefits of the hint. The classic example would be a developer coding hints based on anything other than a perfect copy of production data. 3) Index renaming can cause real grief. In a test environment recently I had to create a subset of data and in my rush / laziness to get the job done asap I renamed the existing table (we needed the data back later on) and created a new table and new indexes. The hints of course then failed and performance died until I realised the problem. Like I said - my motto is that if it doesn't require a hint then don't add one. Some people wisely mentioned that there are categories of hints - those which add a requirement (like append, first_rows and to a lesser degree parallel) are pretty safe, whereas those which dictate execution (like index and use_hash) are a bit more risky. Finally, you can sometimes use hints (like index_desc and index_asc, combined with rownum = 1) to get a minimum or maximum value without using a sort operation - and these can give tremendous performance gains for relatively little risk. Regards, Mark. "Stephane Paquette" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> rdlife.ca> cc: Sent by: [EMAIL PROTECTED] Subject: RE: Oracle position on hints 11/03/2003 08:15 Please respond to ORACLE-L Congratulations ! I had never thought that my question on hints would have generated so much trafic. Stephane -----Original Message----- McDonald Sent: Saturday, March 08, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Just to add my meaningless diatribe to the discussion, I think that there are "good" hints and "bad" hints. For example, first_rows(n) is a good hint, in that you are giving the optimizer more information as to what your motivations are for this query, without stifling it into a choice that may not be appropriate down the track. Hints such as index(tab,idx) are "bad" hints, because you are more exposed to grief. If the index is renamed, then its no longer a hint. If the data distribution changes, or the index is rebuilt in reverse or compressed, then it may no longer be a good option, etc. In terms of the vendor speak of "hints imply weak optimizer" I think thats nonsense. Having worked with DB2 in the past, nothing used to be more irritating then coming up with bizarre convolutions on an otherwise simple SQL to ensure that certain indexes were or were not chosen. Cheers Connor PS - Its probably a little sad that I'm chit-chatting on hints on the morning of my wedding day, but hey, you gotta pass the time somehow whilst the other half is getting the hair and makeup done :-) --- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote: > It's like the Auto-pilot > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > Sent: 3/8/03 2:13 PM > > Stephane > I don't see how Oracle could ever have an > official position on hints. > If > they say hints are going away, many developers > panic. If they say hints > are > great, then Oracle competitors will claim Oracle has > a bad optimizer. > A key political issue in some shops is "Using > hints makes our code > Oracle-specific". My reply is that hints are only > comments that another > database will ignore. > > Dennis Williams > DBA, 40%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -----Original Message----- > Sent: Friday, March 07, 2003 12:19 PM > To: Multiple recipients of list ORACLE-L > > > Hi, > > I have to write a SQL and PL/SQL guide for our > developpers. > Some are good but most of them can use help. > > I'm not a big fan of hints except for insert append, > I used them only if > I > really need to. > > In last october I went to a 3 days DB2 UDB course, > unless I miss > something > there are no hint with DB2 UDB. > So I was thinking that when the Oracle optimiser > would be more mature > maybe > hints would go away. > > I just want to know what is Oracle direction with > hints. > > TIA > > Stephane > > > -----Original Message----- > Robert - IL > Sent: Friday, March 07, 2003 12:01 PM > To: Multiple recipients of list ORACLE-L > > > This is facinating, where does this come from? This > is the second time > in a > week that I've heard this statement being made by > someone. Hints will > never > go away IMHO, and Oracle continues to add more and > more of them. I was > told > by someone at a client site that they were told by > an Oracle instructor > that > they should not use hints in 9i.... > > RF > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > Sent: 3/7/2003 10:04 AM > > Hi, > > Does Oracle have an official position on hints ? > Will they go away as the optimiser is becoming > bettre or they are there > to stay ? > > TIA > > > > Stephane Paquette > > > Administrateur de bases de donnees > > Database Administrator > > Standard Life > > www.standardlife.ca > > Tél. (514) 925-7187 > > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Freeman Robert - IL > 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: Stephane Paquette > 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: Khedr, Waleed > 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). > ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Stephane Paquette 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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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).