Thanks, Wolfgang! I really hope to meet both you and Cary one of these days.
Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -----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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
