Stephane, The queries will almost always include data from the data blocks in addition to the index information. The only query I can think of where that would not be true would be a simple count of orders by date.
I'm beginning to think, based on this discussion and others we've had here where it's been proven that a full table scan is more efficient than an index lookup plus data block read, that perhaps NO index would be best. We are talking row counts in the (at most) 10's of thousands, not millions. I wish it were millions of rows, that would mean the store site was making money :) Rachel --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > Rachel, > > You are right about 'skip scan' being a way to scan the index > rather than something else. In fact, it's an improvement on a full > index scan. > IMHO, since you say that the volume is not that big, there is no > such thing as giving it a try, and possibly comparing it to a full > scan. An index scan (skip or not skip) makes sense if the index is > much smaller than the table. If there is no enormous difference, and > if your query requires other columns than the ones in the index, the > cost of scanning the index plus fetching data blocks (especially if > the clustering factor is low) may well be higher that the cost of > scanning the table in the first place - when I say 'cost', read > 'elapsed time' more than any obscure CBO cooking recipe. > I would naturally tend to consider either two indices or none at > all. > > SF > > >----- ------- Original Message ------- ----- > >From: Rachel Carmichael <[EMAIL PROTECTED]> > >To: Multiple recipients of list ORACLE-L > ><[EMAIL PROTECTED]> > >Sent: Wed, 28 May 2003 02:59:39 > > > >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 > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > 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).
