Are you sure? I've seen two very common examples in the past which lead people to think that hints are ignored when in fact they have not been:
Example 1: In SQL, select /*+HINT*/ will work, but in PL/SQL, select /*+HINT*/ fails since the space trailing the + is missing. Example 2: select /*+ USE_MERGE(e) */ ... from DEPT d, EMP e can come back with a hash join - but the hint has NOT been ignored, it has been 'avoided'. USE_MERGE simply says if EMP is the *target* in the join, then make sure its a sort-merge. That of course does not stop EMP being the *driver* table in a hash join. hth connor --- "Miller, Jay" <[EMAIL PROTECTED]> wrote: > Do you know in what version this became the case? I > certainly remember some > hints being ignored in version 8.0. > > -----Original Message----- > Sent: Saturday, June 29, 2002 5:58 AM > To: Multiple recipients of list ORACLE-L > > > A hint is absolutely, positively a DIRECTIVE, not a > suggestion. > > Jonathan can explain if you have doubts. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic, Jul 9-11 New York City > - Hotsos Clinic, Jul 23-25 Chicago > - 2003 Hotsos Symposium on OracleR System > Performance, Feb 9-12 Dallas > > > > -----Original Message----- > Sent: Friday, June 28, 2002 7:08 PM > To: Multiple recipients of list ORACLE-L > > Hints used to be more like that. > > hints are more like "do it" nowadays, their > aggressiveness has gone up. > > joe > > > basher 59 wrote: > > > When putting in hints, remember this bit of > advise. A hint is just a > > > hint. Oracle may choose today to use your hit and > tomorrow it may > > not. I don't like using hints for this reason, > and I usually try and > > > rewrite the query if I can. Have you analyzed > the table lately. If > > the table is not analyzed, it may not use the > indexes. > > > > > >> From: Robertson Lee - lerobe > <[EMAIL PROTECTED]> > >> Reply-To: [EMAIL PROTECTED] > >> To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >> Subject: Hints being ignored > >> Date: Fri, 28 Jun 2002 06:13:19 -0800 > >> > >> All > >> > >> Oracle 8.0.5.0.0 > >> > >> Tru64 4.0f > >> > >> Select statement as follows. The hint is being > totally ignored. Any > >> ideas ?? > >> > >> Regards > >> > >> Lee > >> > >> SELECT /*+ INDEX(CONSUMER_COMM_D2C > CONSUMER_COMM_D2C_PK) */ > >> i.household_id > >> || '|' || 'd' > >> || '|' || d.promotion_item_id > >> || '|' || xp.campaign_id > >> || '|' || xp.document_id > >> || '|' || xp.segment > >> || '|' || xd.contact_type_code > >> || '|' > >> FROM genex_individual i > >> , consumer_comm_d2c d > >> , x_promotion_item xp > >> , x_document xd > >> WHERE i.brief_name = '$brief_name' > >> AND i.individual_id = d.consumer_id > >> AND d.promotion_item_id = xp.promotion_item_id > >> AND xp.document_id = xd.document_id > >> ORDER > >> BY i.household_id > >> ; > >> > >> Table CONSUMER_COMM_D2C is as follows > >> > >> Name Null? Type > >> ------------------------------- -------- ---- > >> CONSUMER_ID NOT NULL > NUMBER(9) > >> PROMOTION_ITEM_ID NOT NULL > NUMBER(9) > >> COMMUNICATION_DATE NOT NULL DATE > >> COMMUNICATION_STATUS_CODE > VARCHAR2(2) > >> COMMUNICATION_DETAIL > VARCHAR2(500) > >> BLOCK_LDT NOT NULL DATE > >> ROW_CHANGE_SOURCE NOT NULL > NUMBER(9) > >> ROW_CHANGE_TIME DATE > >> ROW_CHANGE_USERID > VARCHAR2(50) > >> > >> Index CONSUMER_COMM_D2C_PK is built as follows > >> > >> consumer_id > >> promotion_item_id > >> communication_date. > >> > >> The table is partitioned on promotion_item_id and > the indexes on the > >> partitions are local > >> > >> Explain plan is as follows > >> > >> > >> Operation Object Name Rows Bytes Cost TQ > In/Out PStart PStop > >> > >> SELECT STATEMENT 1440 G 106 G > >> > >> SORT ORDER BY 1440 G 171770G 106 G > >> > >> MERGE JOIN 1440 G 171770G 17 M > >> > >> SORT JOIN 13 M 1G 300284 > >> > >> HASH JOIN 13 M 1G 10271 > >> > >> TABLE ACCESS FULL X_PROMOTION_ITEM 7 K 409 K > 10 > >> > >> MERGE JOIN CARTESIAN 2 M 90 M 3206 > >> > >> TABLE ACCESS FULL X_DOCUMENT 1 K 25 K 6 > >> > >> SORT JOIN 1 K 31 K 3200 > >> > >> TABLE ACCESS FULL GENEX_INDIVIDUAL 1 K 31 K 2 > >> > >> SORT JOIN 1 G 39G 17 M > >> > >> PARTITION CONCATENATED 1 26 > >> > >> TABLE ACCESS FULL CONSUMER_COMM_D2C 1 G 39G > 1352283 1 26 > >> > >> > >> > >> > >> > >> > ********************************************************************* > >> > >> The information contained in this communication > is > >> confidential, is intended only for the use of the > recipient > >> named above, and may be legally privileged. > >> If the reader of this message is not the intended > >> recipient, you are hereby notified that any > dissemination, > >> distribution, or copying of this communication is > strictly > >> prohibited. > >> If you have received this communication in error, > >> please re-send this communication to the sender > and > >> delete the original message or any copy of it > from your > >> computer system. Thank You. > >> > > > > > > > > > > Over and out Basher 59 > > > > > > > _________________________________________________________________ > > MSN Photos is the easiest way to share and print > your photos: > > http://photos.msn.com/support/worldwide.aspx > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Joe Testa > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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.com > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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.com > -- > Author: Miller, Jay > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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 http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ 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.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
