Mark, thanks, interesting article. But I'm still getting the feeling that the index skip scan is helpful only when you don't want to create a secondary index on columns that are not the left-most column. since I believe that we will be doing a LOT of queries by order date as well, I'm not sure that the benefits of defaulting to index skip scan outweigh the benefits of just having the second index.
Rachel --- Mark Leith <[EMAIL PROTECTED]> wrote: > Rachel, > > http://technet.oracle.com/oramag/webcolumns/2003/techarticles/schumacher_ski > pscan.html > http://www.oracle-base.com/Articles/9i/IndexSkipScanning.asp > > I don't have any personal experience with them myself :( The first > link > gives a pretty good overview though.. > > Mark > > -----Original Message----- > Carmichael > Sent: 28 May 2003 12:00 > To: Multiple recipients of list ORACLE-L > > > 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). > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003 > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mark Leith > 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).