Oracle-L: Re: Hints being ignored

From: <Jared.Still_at_radisys.com>
Date: Mon, 01 Jul 2002 10:19:42 -0800
Message-ID: <F001.0048D069.20020701101942@fatcity.com>

Lee, Lee, Lee...

This is a serious RTFM question. :)

It should read:

SELECT /*+ INDEX(d CONSUMER_COMM_D2C_PK) */

Jared

Robertson Lee - lerobe <lerobe_at_acxiom.co.uk> Sent by: root_at_fatcity.com
06/28/2002 07:13 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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: Jared.Still_at_radisys.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Jul 01 2002 - 13:19:42 CDT