I'm not talking about the cost either. The way by which is getting executed is by reading the whole index. You may call it fast full scan, you may call it index skip scan, but it is still the same thing: sequential read of the whole index. In other words, the name doesn't matter.
Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -----Original Message----- Sent: Wednesday, May 28, 2003 3:40 PM To: Multiple recipients of list ORACLE-L I'm talking about the way it get executed not the statistics or the cost. The cost is completely dependent on the distribution of the data. For example if we have table (c1 number, c2 number) and a primary key on (c1, c2). And the data looks like this: c1 c2 A 1 A 2 A 3 A 4 . . . . A 9999 A 10000 B 1 B 2 B 3 . . . . . . B 9999 B 10000 And I run this sql using skip scan: select c1,c2 from table where c2 = 100 This will be almost similar if you execute this (two unique lookups): select c1,c2 from table where c1 = 'A' and c2 = 100 union all select c1,c2 from table where c1 = 'B' and c2 = 100 There will be extra cost related to finding the unique value of c1 but will be much cheaper compared to full index scan. Regards, Waleed -----Original Message----- Sent: Wednesday, May 28, 2003 2:52 PM To: Multiple recipients of list ORACLE-L True enough, it will show as "index skip scan", but if you take a look at the statistics, you'll see that the nubmer of blocks read roughly corresponds to the number of blocks in the index. It is also logical, because without the first column, the only way to find the desired key is to read the whole index. Indexes are B*tree structures which are searched using modified version of binary search. The ordering is so called lexicographical order, which means that the column 1 is compared first, then column 2 if there is equality in the column 1 and so forth until we reach differing columns. Without knowing column 1, you MUST read them all and see which ones contain the sought for column 2. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -----Original Message----- Sent: Wednesday, May 28, 2003 2:17 PM To: Multiple recipients of list ORACLE-L Skip scan will show in the execution plan as "skip scan". Not true that it will show as regular index scan. Waleed -----Original Message----- Sent: Wednesday, May 28, 2003 1:20 PM To: Multiple recipients of list ORACLE-L A skip scan can be a index scan, full scan or range scan type access. It simply allows a unusable column to be "deselected" from the index (for lack of a better word) during these operations. RF -----Original Message----- To: Multiple recipients of list ORACLE-L Sent: 5/28/2003 11:15 AM A short cut to test the new feature is using the hint index_ss(table,index). Index skip scan is not an index scan or fast full scan. Regards, Waleed -----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: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Gogala, Mladen 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: Khedr, Waleed 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: Gogala, Mladen 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).
