Re: RULE hint

> Those are my two extremes. Supposedly, now, when I add the two
> together, RULE should override the other hint.
>
> SELECT /*+ RULE INDEX(Moo Cow) */ * FROM Moo;
>
> However, that does an INDEX FULL SCAN.
>
> It can't be cancelling it out, because when I give confilicting hints
> it ignored both, as it should.
>
> SELECT /*+ RULE ALL_ROWS */ * FROM Moo;
>
> Does a TABLE ACCESS FULL.
>
> Am I misunderstanding something?

Yes, you are actually. Any hint other than "rule" will invoke the cost based optimizer. So, the rule hint overrides nothing, it just gets ignored.

The nature of rule based optimization is that it cannot respond to hints. It has hard coded rules and follows them. So, for two equally preferred indexes, it will simply choose the first one it sees. In your first case, it sees no predicate, so knows to do a full table scan.

Now, let me ask this: if you don't have a predicate, why would you want to get to the table via an index anyway? You just incur a bunch more overhead for nothing.

• ricky