Re: Hints being ignored

Hints used to be more like that.

hints are more like "do it" nowadays, their aggressiveness has gone up.

joe

basher 59 wrote:

> 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: Joe Testa
  INET: jtesta_at_dmc-it.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).