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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > __________________________________ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).