Are you sure? I've seen two very common examples in
the past which lead people to think that hints are
ignored when in fact they have not been:
Example 1:
In SQL, select /*+HINT*/ will work, but in PL/SQL,
select /*+HINT*/ fails since the space trailing the +
is missing.
Example 2:
select /*+ USE_MERGE(e) */ ...
from DEPT d, EMP e
can come back with a hash join - but the hint has NOT
been ignored, it has been 'avoided'. USE_MERGE simply
says if EMP is the *target* in the join, then make
sure its a sort-merge. That of course does not stop
EMP being the *driver* table in a hash join.
hth
connor
- "Miller, Jay" <JayMiller_at_TDWaterhouse.com> wrote:
> Do you know in what version this became the case? I
> certainly remember some
> hints being ignored in version 8.0.
>
> -----Original Message-----
> Sent: Saturday, June 29, 2002 5:58 AM
> To: Multiple recipients of list ORACLE-L
>
>
> A hint is absolutely, positively a DIRECTIVE, not a
> suggestion.
>
> Jonathan can explain if you have doubts.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Jul 9-11 New York City
> - Hotsos Clinic, Jul 23-25 Chicago
> - 2003 Hotsos Symposium on OracleR System
> Performance, Feb 9-12 Dallas
>
>
>
> -----Original Message-----
> Sent: Friday, June 28, 2002 7:08 PM
> To: Multiple recipients of list ORACLE-L
>
> 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).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.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).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 - 12:28:47 CDT
