Rachel, First, I'd strongly suggest you look at 9.2.0.3... there are a number of bug fixes in it and we have been running it here for a couple of months now I would say with few problems.
With regards to the skip scans on indexes, your assumptions are correct. I've seen some cases where skip scans made rather inefficient SQL much more efficient and of course I've seen the reverse. Of course, when using 2 indexes, you have the potential for more IO, depending on the structure of the indexes, how Oracle does the join, etc.... Depending on the order and cardinality of the column in question that would be skipped, it index may perform just fine via skip scan, or it might be a dog. We had one untuned once-a-day, untuned SQL statement runing in production start performing like lighting when we moved the DB to 9i from 8i. After looking at the explain plan I found out that it started doing a skip scan on an index that previously was unavailable to Oracle because of some date function manipulation in the WHERE clause. Once Oracle could just "skip" that date column, wammo, we got single index usage and great response times. I generally prefer single index lookups vs. multipule index lookups, and my experience is that single index scans perform better than say AND_EQUAL operations. My opinion, could always be WRONG (and have been in the past). Robert -----Original Message----- To: Multiple recipients of list ORACLE-L Sent: 5/28/2003 9:40 AM 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL 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).
