RE: Hints being ignored

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Connor
> McDonald
> Sent: Monday, July 01, 2002 12:29 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Hints being ignored
>
>
> 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.

And one of the reasons it is recommended to use the ORDERED hint when specifying join methods. There is nothing to prevent the CBO, as Connor pointed out, from calculating costs using other join orders, as is its normal behavior. The hint didn't *exclude* the evaluation of the various combinations of driving orders of tables. I think this is the most common example I see -- ignoring join methods when the person failed to specify the ORDERED hint.

And another more subtle one is even if E, in the above example, is *not* the driving table in the plan and yet a HASH JOIN instead of a MERGE JOIN is used. What happens there is that the CBO evaluates driving by E, deciding on an HJ and throwing a "swap sides" at you.

Larry G. Elkins

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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).