Re: Hints and statistics

On Sat, 05 Feb 2000 10:25:43 GMT, mdraisma.dit.verwijderen_at_multiweb.nl (Marko Draisma) wrote:

>Sometimes Oracle overrides a hint for a query. For example: yesterday one of
>my students tried to force oracle to do a nested-loops query, but after the
>EXPLAIN PLAN statement Oracle seemed to choose a full-table scan.
>Then we deleted the statistics for the table. After that, Oracle followed our
>hint.
>
>Is this the way Oracle works? With statistics Oracle chooses, without
>statistics I can choose?
>
>Marko.

Hi Marko,
Note : hints are very sensitive to syntax. If you use an alias in your from list, you MUST use the alias in the hint, or it won't work. Also there seems to exist a bug in AFAIK 8.0.5: a space between the /*+ and the hint is mandatory there.

The way Oracle works is:
with statistics:
Oracle will use the cost based optimizer

with statistics and hints
Oracle will use the cost based optimizer and ocassionally ignore the hint

without statistics and without hints:
Oracle will use the rule based optimizer (where the way you write your query will influence the plan)

without statistics and with hints:
Oracle will use the cost based optimizer and try to determine statistics on the fly.

So this is sheer luck, and chances are removing the hint will not change the plan.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Feb 05 2000 - 05:19:35 CST