Re: Hints being ignored

From: basher 59 <kb7yss_at_hotmail.com>
Date: Fri, 28 Jun 2002 13:58:29 -0800
Message-ID: <F001.0048BE92.20020628135829@fatcity.com>

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 <lerobe_at_acxiom.co.uk>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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
>
>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.
>

Over and out Basher 59

---

MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: basher 59
  INET: kb7yss_at_hotmail.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 Fri Jun 28 2002 - 16:58:29 CDT