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