Kevin, Thanks.... these will NOT be ad-hoc queries but part of the app -- for the admin and customer service users.
I'm leaning more and more towards setting things up so that we either do a full table scan or use two indexes. I just did a query -- since the app was released in December, we have had just over 24,000 rows added to the order table. Trifling.... even if we do an FTS Rachel --- Kevin Toepke <[EMAIL PROTECTED]> wrote: > Rachel > > My experience with index skip scans can be summed up as follows. If > you know > the app will be doing a particular scan, create the index. > > Index Skip Scans should be thought of a means to help optimize those > pesky > ad-hoc queries only. > > I haven't been able to get a skip-can to work unless there is a > simple > restriction (>, <, =) on the non-leading column. My experience tells > me they > don't help when you are joining against a non-leading column or you > are > using an IN condition (either static or sub-query) > > HTH > Kevin > > -----Original Message----- > Sent: Wednesday, May 28, 2003 7:00 AM > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kevin Toepke > 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).