Re: skip scan index

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