ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 全表扫描,索引范围扫描与块的理解

oracle 全表扫描,索引范围扫描与块的理解

原创 Linux操作系统 作者:paulyibinyi 时间:2008-04-18 10:42:58 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-243694/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6504502