Here is the idea: Index test_skip1 is located in the tablespace INDX which has one file, FILE#=5
I restart the database, execute your query, then see V$FILESTAT for blocks read. (select PHYBLKRD from v$filestat where file#=5;) Then restart the database, execute query asking for a fast full scan and see how many blocks do get read. If the number is the same, then the conclusion is inevitable. So, here we go: SQL> set autotrace on explain SQL> select /*+ index_ss(test_skip1 ) */ c1,c2 from test_skip1 where c2 = 100; 2 3 C1 C2 ---------- ---------- 1 100 2 100 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78 52) 1 0 INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car d=302 Bytes=7852) SQL> select PHYBLKRD from v$filestat where file#=5; PHYBLKRD ---------- 10 <---DATABASE RESTART---> Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning option JServer Release 9.2.0.3.0 - Production SQL> set autotrace on SQL> select /*+ index_ffs(t test_skip1_pk ) */ c1,c2 from test_skip1 t where c2 = 100; 2 3 C1 C2 ---------- ---------- 1 100 2 100 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785 2) 1 0 INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4 Card=302 Bytes=7852) Statistics ---------------------------------------------------------- 300 recursive calls 0 db block gets 777 consistent gets 724 physical reads 0 redo size 464 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 2 rows processed SQL> select PHYBLKRD from v$filestat where file#=5; PHYBLKRD ---------- 722 That means that fast full scan will read 722 blocks where skip scan will read only 10, which means that you were right and I was wrong. Obviously, my metodology was incorrect or 9.2.0.1 database that I've tested it on has had a bad bug, which would really be surprising and unusual. Anyway, you are right. That, in turn, implies that oracle indexes are not classic B*Tree structures as I was lead to believe but are spiked with an unknown liquor. Thanks for helping me clarify this. On 2003.05.28 18:29 "Khedr, Waleed" wrote: > It's like any other execution plan, good in certain data distributions and > bad in others. > > But I do not think it's correct that skip scan requires reading the whole > index (it's even clear in this test). > > Waleed > > -----Original Message----- > Sent: Wednesday, May 28, 2003 5:30 PM > To: Multiple recipients of list ORACLE-L > > > OK. I don't have the 9i instance that I can use for testing right now, > but tonight, at home, I'll give you the counter example. The bottom line > is that the only way to execute a skip scan with a B*Tree index is to > go and read it whole. No other way. > > Mladen Gogala > Oracle DBA > Phone:(203) 459-6855 > Email:[EMAIL PROTECTED] > > > -----Original Message----- > Sent: Wednesday, May 28, 2003 4:40 PM > To: Multiple recipients of list ORACLE-L > > > Not true, try this: > > create table test_skip1 ( c1 number,c2 number, primary key (c1,c2)); > > begin > for i in 1..100000 loop > insert into test_skip1 values (1,i); > insert into test_skip1 values (2,i); > end loop; > end; > > > alter session set sql_trace = true; > > select --+ index_ss(test_skip1, ) > c1,c2 > from test_skip1 > where c2 = 100; > > select blocks from dba_segments where segment_name = 'SYS_C0038241' -- pk > index > blocks = 384 > ----------------------------------- > -- From the tkprof output > ----------------------------------- > select --+ index_ss(test_skip1, ) > c1,c2 > from test_skip1 > where c2 = 100 > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 1 0.01 0.00 0 0 0 > 0 > Execute 1 0.00 0.00 0 0 0 > 0 > Fetch 1 0.00 0.00 0 8 0 > 2 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 3 0.01 0.00 0 8 0 > 2 > > Misses in library cache during parse: 1 > Optimizer goal: CHOOSE > Parsing user id: 26 (IA) > > Rows Row Source Operation > ------- --------------------------------------------------- > 2 INDEX SKIP SCAN SYS_C0038241 (cr=8 r=0 w=0 time=1226 us)(object id > 810709) > > > Rows Execution Plan > ------- --------------------------------------------------- > 0 SELECT STATEMENT GOAL: CHOOSE > 2 INDEX (SKIP SCAN) OF 'SYS_C0038241' (UNIQUE) > > --------------------------- > --------------------------- > -- This is using index scan > --------------------------- > --------------------------- > > select --+ index( test_skip1, ) > c1,c2 > from test_skip1 > where c2 = 100 > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 1 0.00 0.00 0 0 0 > 0 > Execute 1 0.00 0.00 0 0 0 > 0 > Fetch 1 0.12 0.11 0 331 0 > 2 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 3 0.12 0.11 0 331 0 > 2 > > Regards, > > Waleed > > -----Original Message----- > Sent: Wednesday, May 28, 2003 4:05 PM > To: Multiple recipients of list ORACLE-L > > > 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). > -- > 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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).