oracle 全表扫描 索引 等介绍,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