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