Re: RULE hint

On Fri, 21 Dec 2001 15:02:57 GMT, Ricky Sanchez <rsanchez_at_more.net> wrote:

>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

Thank you for the explanation. That's what I wanted.

I actually have no such table. I created it temporarily for testing, to help me understand what I am reading in the documentaiton.

Now to post another question.

Brian Received on Fri Dec 21 2001 - 09:40:09 CST