oracle 全表掃描,索引範圍掃描與塊的理解

SQL> create table t as select  * from dba_objects;

sql>analyze table t compute statistics;

SQL> select count(distinct b) from
  2    (select dbms_rowid.rowid_block_number(rowid) b from t)
  3  ;
 
COUNT(DISTINCTB)
----------------
              76                          可以看到這個表t分配了76個塊

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 18 10:34:24 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> set autot on
SQL> select object_id,object_name from t where object_id=6318;

 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------

      6318
T2

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=1 Bytes=19)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=1 Bytes=19)

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         80  consistent gets    全表掃描80個邏輯讀因為可能需要讀取其他一些表相關資訊,多幾個塊正常的
          0  physical reads
          0  redo size
        443  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index idx_test on t(object_id);

Index created.

SQL> analyze table t compute statistics for table for all indexed columns;

Table analyzed.

SQL> select object_id,object_name from t where object_id=6318;

 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------

      6318
T2

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=19)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          19)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE) (Cost=1 Ca
          rd=1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets       利用索引馬上能讀到指定的塊 這也就是利用索引快的原因
          1  physical reads   第一次讀取  需要從硬碟讀到緩衝區
          0  redo size
        443  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

SQL> select object_id,object_name from t where object_id=6318;

 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------

      6318
T2

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=19)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          19)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE) (Cost=1 Ca
          rd=1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets      
          0 physical reads   第二次讀取 就不需要硬碟讀取了,直接在data buffer中讀了
          0  redo size
        443  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed