Re: RULE hint

Brian-

I see your point now. I suspect either a product bug or a doc bug, if your testing is correct.

However, to get a fast full scan, you need CBO. And the hint is INDEX_FFS, not INDEX. An index full scan is like a range scan, but includes all index values. It starts with the root block and works down through the branches and does an ordered read of the blocks, typically one at a time. A fast full scan simply rips through the index blocks in multi-block swipes, then ignores the root and branches, returning the result set directly from the leaf blocks, not necessarily in logical order.

RBO does not support fast full scans at all, so CBO is necessary. Also, it is not necessarily the case that a fast full scan will be faster. In your case, where there is only one column in the table, the index might actually be larger than the table itself. You not only have the root and branch blocks to be read and ignored, you also have rowid pointer structures carried in each leaf block, eating up space. It's a matter of relative data density.

You might analyze and check the block count of the index and compare it to the table. If the table is smaller, just do a full table scan. In cases where a table is large - and tables normally have many more columns than your case - the ffs approach is good, especially because Oracle will do multi-block reads on the index, which minimizes IO.

So, while the "bug" is interesting, it is sort of beside the point for your stated goal.

• ricky

Brian Tkatch wrote:
>
> 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 Fri Dec 21 2001 - 09:02:57 CST