Re: Don't rely on 9i Skip Scan Indexing

"grjohnson" <Johnsog123_at_hotmail.com> wrote in message news:32b8a689.0210202017.1400c57e_at_posting.google.com...
> G'day,
>
> Skip Scanning of Indexes is a new feature of Oracle 9i. In previous
> releases of Oracle, if a query failed to utilise an index prefix i.e.
> the leading column of the composite index, the CBO would perform a
> Full table Scan (FTS) and would cause the index not to be used.
>
> Sorry Howard Rogers, but I'm going to quote you here, in Oracle9i
> Index Skip Scanning is "an intrinsic function of the cost-based
> optimizer... It will only be of significant benefit where the leading
> column of the index is of fairly low cardinality, and whether the
> optimizer ever uses it depends entirely on the nature of the SQL
> statments thrown at the database."
>

Hi Greg,

Howard's quote is pretty spot on.

The cardinality of both the leading (especially) and subsequent columns are important for this to take effect. If the cardinality of the leading column is too high, then not enough leaf blocks are likely to be skipped for this to be beneficial. If the cardinality of the subsequent columns is too low, then it's questionable whether the use of the index is going to be beneficial.

Although I can see various scenarios where this feature could be useful, it's usually going to be hard pressed out performing either a Fast Full Index Scan or indeed a Fast Full Table Scan.

As always, does the cost of reading the index in this somewhat inefficient manner outweigh the other options. I certainly wouldn't rely on this option but it is nice that at least it is now another option.

Cheers

Richard Received on Mon Oct 21 2002 - 08:59:01 CDT