RE: Hints being ignored
I'm on 8.1.6 and the other day I was trying to use a hint on the GL_INTERFACE table from Oracle Financials (i know OF is rule based)that I had copied to another database instance and I couldnt get a hint to work. And i know that Hint is not SQL supported and therefore the syntax has to be 100% correct. I was busy so I jut deleted records I didnt need but I will go back and try again later. On the whole though using 8.1.6 Ive remember situations where hints have not taken. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Hints being ignored
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Connor > McDonald > Sent: Monday, July 01, 2002 12:29 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Hints being ignored > > > 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. And one of the reasons it is recommended to use the ORDERED hint when specifying join methods. There is nothing to prevent the CBO, as Connor pointed out, from calculating costs using other join orders, as is its normal behavior. The hint didn't *exclude* the evaluation of the various combinations of driving orders of tables. I think this is the most common example I see -- ignoring join methods when the person failed to specify the ORDERED hint. And another more subtle one is even if E, in the above example, is *not* the driving table in the plan and yet a HASH JOIN instead of a MERGE JOIN is used. What happens there is that the CBO evaluates driving by E, deciding on an HJ and throwing a "swap sides" at you. Larry G. Elkins -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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).
Re: Hints being ignored
Lee, Lee, Lee... This is a serious RTFM question. :) It should read: SELECT /*+ INDEX(d CONSUMER_COMM_D2C_PK) */ Jared Robertson Lee - lerobe <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 06/28/2002 07:13 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Hints being ignored 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 NameNull?Type --- CONSUMER_ID NOT NULL NUMBER(9) PROMOTION_ITEM_ID NOT NULL NUMBER(9) COMMUNICATION_DATE NOT NULL DATE COMMUNICATION_STATUS_CODEVARCHAR2(2) COMMUNICATION_DETAIL VARCHAR2(500) BLOCK_LDT NOT NULL DATE ROW_CHANGE_SOURCE NOT NULL NUMBER(9) ROW_CHANGE_TIME DATE ROW_CHANGE_USERIDVARCHAR2(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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
RE: Hints being ignored
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 > >> > >> NameNull?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
RE: Hints being ignored
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 >> >> NameNull?Type >> --- >> CONSUMER_ID NOT NULL NUMBER(9) >> PROMOTION_ITEM_ID NOT NULL NUMBER(9) >> COMMUNICATION_DATE NOT NULL DATE >> COMMUNICATION_STATUS_CODEVARCHAR2(2) >> COMMUNICATION_DETAIL VARCHAR2(500) >> BLOCK_LDT NOT NULL DATE >> ROW_CHANGE_SOURCE NOT NULL NUMBER(9) >> ROW_CHANGE_TIME DATE >> ROW_CHANGE_USERIDVARCHAR2(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
RE: Hints being ignored
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 >> >> NameNull?Type >> --- >> CONSUMER_ID NOT NULL NUMBER(9) >> PROMOTION_ITEM_ID NOT NULL NUMBER(9) >> COMMUNICATION_DATE NOT NULL DATE >> COMMUNICATION_STATUS_CODEVARCHAR2(2) >> COMMUNICATION_DETAIL VARCHAR2(500) >> BLOCK_LDT NOT NULL DATE >> ROW_CHANGE_SOURCE NOT NULL NUMBER(9) >> ROW_CHANGE_TIME DATE >> ROW_CHANGE_USERIDVARCHAR2(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 Ph
Hints being ignored
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.
