RE: skip scan index

Thanks, Wolfgang! I really hope to meet both you and Cary one of these days.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:mgogala_at_oxhp.com

-----Original Message-----
Sent: Wednesday, May 28, 2003 11: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: 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).