| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: skip scan index
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:mgogala_at_oxhp.com
>
>
> -----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:mgogala_at_oxhp.com
>
>
> -----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:mgogala_at_oxhp.com
>
>
> -----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: wisernet100_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: FREEMANR_at_tusc.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
>
> 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: ListGuru_at_fatcity.com (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: Waleed.Khedr_at_FMR.COM
>
> 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: ListGuru_at_fatcity.com (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: mgogala_at_adelphia.net 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: ListGuru_at_fatcity.com (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).Received on Wed May 28 2003 - 21:08:28 CDT
 |
 |