RE: skip scan index

Mladen,

I think they're ever-so-slightly spiked B*-trees. If you analyze the 10046 level-8 trace data carefully, perhaps you'll find that the skipping is taking place using information that is available in the branch blocks.

I believe that your query required a depth-first probe for each distinct value of c1, and then a left-to-right leaf scan for c2 values matching your c2=100 predicate. You could see everything by studying a block dump of the index if you wanted...

I'm impressed that the world contains people willing to do these kinds of tests and others willing to provide feedback, and that there's a great venue through which to share the results. I feel like it's a big change from just five years ago!

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details...

-----Original Message-----
Breitling
Sent: Wednesday, May 28, 2003 10:05 PM
To: Multiple recipients of list ORACLE-L

I was about to post the results of my test which also did prove you wrong. I ran the test with a 10046 level 8 trace to show the individual index block reads which nicely show why it is called a skip scan, but since you already proved yourself wrong there is no need.

BTW, as of Oracle 9 you don't necessarily need to restart the database to reset the pools. This should do the trick:

ALTER SESSION SET EVENTS 'immediate trace name flush_cache'; alter system flush shared_pool;

At 06:08 PM 5/28/2003 -0800, you wrote:
>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.
>

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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: Cary Millsap
  INET: cary.millsap_at_hotsos.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).