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