Hints being ignored

[Robertson Lee - lerobe]

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.