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