Re: RULE hint

On Thu, 20 Dec 2001 21:52:48 GMT, Ricky Sanchez <rsanchez_at_more.net> wrote:

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

Ah, so the documentation is incorrect in stating

"It also makes the optimizer ignore other hints specified for the statement block.".

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

I am reading the optimization manual, and I am trying to understand what I read. To do that, I create table and see what happens. When something goes in a way that I did not expect, I ask.

Being the index is a PRIMARY KEY, and there is only one column, all the values for the * are in the index. Which means, that only an INDEX FAST FULL SCAN is needed. Is that any slower than a FULL TABLE SCAN?

Brian Received on Thu Dec 20 2001 - 16:31:43 CST