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

> Which bit of the above don't you like?! "it's an intrinsic function of the
> CBO" means you don't have a SKIP_SCAN=TRUE parameter to set. "leading column
> is low cardinality" is true, to the extent that if you have a high
> cardinality column as the leading edge, you are most unlikely to end up
> skipping anything very much.

Howard, I didn't say I "didn't like any of your comments", I was just quoting your explanation of skip scan indexing.
> >
>
> > I took a table and created a composite index. The table had only 66
> > rows.
>
> Extremely bad news. If you're going to get skip scanning happening, you
> need an index with plenty of leaf nodes so that there's actually something
> worth skipping! 66 rows probably creates an index with 4 blocks tops (give
> or take). Not much point in skipping anything there (and yes, I know the
> example I gave in the book used an index of 8 leaf nodes, but that was just
> to get things explained in theory). Try your tests with 6600 rows and
> report back. Your failure to induce the CBO to start skipping is entirely a
> product of this one aspect of your test.

"Your failure"... harsh, but note taken.
>
> I can't remember exactly, and it's been a few months, but I could have sworn
> my examples in the Beginning Programming book asks you to create a table as
> a select from dba_objects -which means that it would have had about 5500
> rows as an absolute minimum.

The only example I saw was from metalink. (And it used a HINT). I gleaned my information from your infamous Oracle 9i New Features .pdf and Oracle Press' poor excuse for 9i New Features exam guide and Oracle's ILT manual, of which, neither give examples of the functionality working i.e. an explain plan of a query.

Thanks for your feedback, I'll be sure to expand my test set.

Thank you,

Greg Johnson
Oracle Certified Professional
OCP 8i DBA / OCP 9i DBA
Dialog Information Technology
Brisbane, Queensland, Australia Received on Mon Oct 21 2002 - 18:02:11 CDT