Oracle-L: RE: skip scan index

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Wed, 28 May 2003 13:29:50 -0800
Message-ID: <F001.005A4E54.20030528132950@fatcity.com>

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.

-----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
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------