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