Re: skip scan index

Hi Mladen,

Not true.

The whole point of the Index Skip Scanning is that Oracle can avoid probes of leaf pages because it knows for sure that the required index value can't possibly be found in a leaf node based on the less than values found in the branch nodes (read my earlier post if it makes any sense).

For a concatenated index artist || album_name and you want all albums called "ALADDIN SANE"
by any artist.

If a leaf node had "BEATLES || SEG PEPPERS|| as it's maximum value and the next leaf node had BEATLES || WHITE ALBUM as it's max values there *can't possible be* an album called ALADDIN SANE in that leaf node and so Oracle doesn't need to probe it.

If however, the next leaf node had a max value of BOWIE || DIAMOND DOGS then this node *could* have a value of ALADDIN SANE so would need to be probed.

Note also that BOWIE || AARDVARK also causes a probe of the leaf node as Oracle can't know for sure that there's no value between BEATLES and BOWIE (BOLAN ||ALADDIN SANE for example)

Of cause a max value of BEATLES || ABBEY ROAD followed by a max value less than equal to BEATLES || SGT PEPPERS will also cause a probe.

So it depends on whether Oracle can know for sure that the value it's looking for can't possibly exist in a leaf node *by just checking the branch node*.

Suggestions that a full index scan and a index skip scan are just the same thing are therefore incorrect.

Cheers

Richard

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 29, 2003 7:29 AM

> 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: Richard Foote
  INET: richard.foote_at_bigpond.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).