RULE hint

The documentation:

Oracle8i Designing and Tuning for Performance Release 2 (8.1.6)
Chapter 7, "Using Optimizer Hints"

Section: Using Hints
Sub-Section: Hints for Optimization Approaches and Goals Paragraph title: RULE

<<<

The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block. The syntax of this hint is as follows:
<<<

So, I tried it. (using 8.1.6)

CREATE TABLE Moo (Moo NUMBER CONSTRAINT Cow PRIMARY KEY);

(The rest means an EXPLAIN PLAN.)

When running a base rule query.

SELECT /*+ RULE */ * FROM Moo;

It does a TABLE ACCESS FULL, as expected.

I tried forcing it to use an index:

SELECT /*+ INDEX(Moo Cow) */ * FROM Moo;

And it did an INDEX FULL SCAN.

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?

Brian Received on Thu Dec 20 2001 - 13:10:33 CST