| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: skip scan index
Richard,
the access would be order date or vendor id/order date (since it's possible to look up by vendor id alone as well)
very low cardinality on vendor id -- right now I have all of two.
personal opinion is that the developer read something "cool" and decided to tell the DBA how to do things, especially since the statement was "create a skip scan index" :)
Rachel
--- Richard Foote <richard.foote_at_bigpond.com> wrote:
> Hi Rachel,
>
> Correct, "Skip Scan Index" is not a type of index but a method
> whereby
> Oracle can eliminate the need to visit leaf nodes by determining
> whether the
> leading column(s) have changed by sussing out only the branch nodes.
> It's
> possibly useful in situations where previously Oracle would not
> consider a
> concatenated index if the leading column of the index is unknown
> whereas now
> the optimizer might determine that sufficient leaf nodes can be
> avoided for
> the index to be of benefit. It's a kinda improved version of the full
> index
> scan (or not so full if you know what I mean),
>
> However this requires the leading column to have *low* cardinality,
> low
> enough for the same repeated column from one leaf node to extent
> across all
> values of it's neighbouring leaf node. If the leading column changes
> from
> one leaf node to the next, then that leaf node must be at least
> visited
> (although subsequent inspection of the index values may enable Oracle
> to
> "pull out early" from having to read all index values, if a
> subsequent
> change in the leading column rules out all remaining entries).
>
> A quick (and nasty) formula would be to consider the ratio of leaf
> nodes to
> distinct values (LN/DV). The higher the ratio the better with any
> value
> somewhat greater than 1 giving a skip scan index path a chance with
> the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
>
> However, getting back to your actual situation, if table access is
> only to
> be made via the order date or by order date and order id (and not
> necessarily by order id only), then you may find a single index order
> date
> || order id would meet all your requirements.
>
> Cheers
>
> Richard Foote
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, May 28, 2003 8:59 PM
>
>
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> >
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> >
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> >
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> >
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> >
> > Rachel
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Richard Foote
> INET: richard.foote_at_bigpond.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: wisernet100_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed May 28 2003 - 09:40:09 CDT
 |
 |