RE: oracle can ignore hints
- From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 11:06:23 +0000
Comments embedded - catching up on 400 messages after 2 days away :(
> -----Original Message-----
> From: Jared.Still@xxxxxxxxxxx
> Sent: 10 March 2004 13:22
> To: Jared.Still@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: Re: oracle can ignore hints
>
>
> Correct, so did Oracle 'ignore' the hint?
>
> I guess the point is this: does Oracle ignore your hints,
> or are they merely unusable?
I don't think that the experiment goes quite far enough.
I have repeated it with the following modification
Run the statement without the hint and c1 is null
Run the statement with the hint and c1 is null.
Next
Run the statement without the hint and c1 is not null
Run the statement with the hint and c1 is not null.
I.E. First see what oracle does on its own, then see what adding the hint does.
Output each of these to a separate 10053 trace file and compare.
In the null case Oracle *never* considers the index (as you should expect).
In the not null case Oracle considers the index and rejects it without the hint
in favour of the FTS, with the hint it only considers the index as an access
path into t.
In other words I think this demonstrates quite well that hints (at least access
path hints) don't ADD new access paths for consideration, but restrict the
available access paths. Thus hinting an index that cannot be used* and so will
never be considered is not (IMO) a case of "ignoring" the hint, but a case of
supplying an invalid hint (just as if I'd supplied select /*+ index (idx t) */
to the query). In general I find this way of thinking about hints (as
restricting the available choices for the optimizer) as quite helpful. It
enables you when the CBO is 'ignoring' your hint to ask questions like - is the
access path that I want actually available, have I restricted the optimizer's
choices enough - as well as giving a hint (sorry for the pun) as to why bright
people often look down on hints or treat them as a quick fix/ workaround. If
you view every hint you give as handicapping Oracle in some way you tend to
avoid sprinkling them liberally thorughout your code.
> You could also try this with a bitmapped index.
>
> Jared
And actually there is a prediction that can be made from the above for this
case. Bitmapped indexes index nulls, therefore the bitmapped index will be
available to the optimizer. Thus I predict that the bitmapped index will be
*considered* without the hint (i.e it will show in a 10053 trace), and will be
*chosen* with the hint.
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805
* NB it is perfectly possible to hint Oracle to use an index that is marked
'UNUSABLE' I think of this as a special case especially as the unhinted
optimizer will also choose an 'UNUSABLE' index. I regard this as a bug - to me
Oracle knows the index is unusable so it should not consider it at all. You can
look at it as a feature (reducing invalidations of execution plans when tables
move/indexes are set unusable).
**********************************************************************
This email contains information intended for
the addressee only. It may be confidential
and may be the subject of legal and/or
professional privilege. Any dissemination,
distribution, copyright or use of this
communication without prior permission of
the sender is strictly prohibited.
**********************************************************************
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
