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

"grjohnson" <Johnsog123_at_hotmail.com> wrote in message news:32b8a689.0210211502.3b57e625_at_posting.google.com...
> > 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.

(Another bit of irony gets lost in translation! The exclamation mark was the clue).

> > >
> >
> > > 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).

OK, here's a hint (!!). Hi thee to the nearest bookstore and lay hands on an excellent tome called Beginning Oracle Programming, published by Wrox Press.

I hear Chapter 9 is rather good.

(End of shameless plug).

> I
> gleaned my information from your infamous Oracle 9i New Features .pdf

Infamous??! Surely not. You meant, I am sure, 'thoroughly readable, approachable, reasonably accurate, and not a bad stab at explaining all the intracacies'.

> 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.

That rather fine book, Beginning Oracle Programming from Wrox Press, available from all good record stores near you (something like that) has an EXCELLENT chapter 9, which includes lots of explain plans.

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

And visit all good book stores near you? To purchase a rather spiffing.... (oh well, you get the idea).

Cheers,
HJR
> Thank you,
>
> Greg Johnson
> Oracle Certified Professional
> OCP 8i DBA / OCP 9i DBA
> Dialog Information Technology
> Brisbane, Queensland, Australia
Received on Tue Oct 22 2002 - 02:08:57 CDT