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

SQL> create table t as select  * from dba_objects;

sql>analyze table t compute statistics;

ITPUB个人空间1Z0K7^/] e0?"F1e&k9y
SQL> select count(distinct b) fromITPUB个人空间 qa+P"I-zrE7C
  2    (select dbms_rowid.rowid_block_number(rowid) b from t)
8n)R)h4z9\9V0  3  ;
0sAE A,tds\0 ITPUB个人空间o RP
p+}.^ g
COUNT(DISTINCTB)ITPUB个人空间De7\6e3Qi
----------------
*pn]If'P }0              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.

ITPUB个人空间)n1N)g/y?8v+\.xa
Connected to:
Vr)d*E+qD^%?f0Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
*r IbE/m0With the Partitioning, OLAP and Oracle Data Mining options
*?&}.}m[U ?#^Fi0JServer Release 9.2.0.4.0 - Production

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

 OBJECT_ID
/@!H_8M/rXqs-bvTZ0----------
_p#?ctf W'l0OBJECT_NAMEITPUB个人空间5k A1dI,h5|
--------------------------------------------------------------------------------

      6318
_5e:OSqG,n1e0T2

Execution Plan
Tv9@;B qy-Z3@0----------------------------------------------------------
GM4I$erkfv0   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=1 Bytes=19)ITPUB个人空间R,][ G BHIBQ
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=1 Bytes=19)

ITPUB个人空间E.si,sfC0{,|j/M
Statistics
FX1bA,TDG0----------------------------------------------------------ITPUB个人空间
@"X? \x/_)}+Dy
          0  recursive callsITPUB个人空间&Q?U7_sn0I:C
          0  db block getsITPUB个人空间 f5h$LB*}#oy
        80  consistent gets    全表扫描80个逻辑读因为可能需要读取其他一些表相关信息,多几个块正常的ITPUB个人空间L{2Z.s&pe
          0  physical readsITPUB个人空间j;L9C(|h[:e4A
          0  redo size
QE#z ~{3a9lX0        443  bytes sent via SQL*Net to clientITPUB个人空间-hv/jC
WEp
        503  bytes received via SQL*Net from clientITPUB个人空间n&H|~;SYw
          2  SQL*Net roundtrips to/from clientITPUB个人空间-M7N&G+P*A
t)w(k2P
          0  sorts (memory)
6{gqu!` x~2Z0          0  sorts (disk)ITPUB个人空间1_6p*X j1B%X;KSO
          1  rows processed

SQL> create index idx_test on t(object_id);

Index created.

ITPUB个人空间G
Qh_F `!i/Z
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_IDITPUB个人空间BOxK6h#f
----------ITPUB个人空间r4b} {Yl
OBJECT_NAME
1?gd(o4L2M-ld1E[0--------------------------------------------------------------------------------

      6318ITPUB个人空间"Ey0_GSC8rn h^
T2

Execution PlanITPUB个人空间4N&PGKs!_!B E
----------------------------------------------------------ITPUB个人空间;c]1{ o{}6?8G~)t#Q
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=19)ITPUB个人空间%zrhz+v*o a od3_
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
"`o0GID(e0          19)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE) (Cost=1 CaITPUB个人空间Jq&\-XK6m0e
          rd=1)

Statistics
'UOLfw x;_B ^(L y0----------------------------------------------------------
)b;h Ei~$o0          0  recursive calls
%V#j
HM5E)S0          0  db block getsITPUB个人空间Pc+P{1H0Gd.k
          4  consistent gets       利用索引马上能读到指定的块 这也就是利用索引快的原因
,U$Q]%y0I0         1  physical reads   第一次读取  需要从硬盘读到缓冲区
7A\q)l#vH6M3Q!F0          0  redo sizeITPUB个人空间+d7E)t Q'X!@L!W
        443  bytes sent via SQL*Net to clientITPUB个人空间v*\zEx0nc1V
W
        503  bytes received via SQL*Net from client
;bC KM s"~0          2  SQL*Net roundtrips to/from client
&F&q&s1bb!r^0          0  sorts (memory)ITPUB个人空间9W.Po(wZO
          0  sorts (disk)
U`G{"ADbd0          1  rows processed

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

 OBJECT_IDITPUB个人空间Q)n(f\t2k\8~
----------ITPUB个人空间 QA*_Z8l,Wwh
OBJECT_NAME
b'u,g`|f)}0--------------------------------------------------------------------------------

      6318ITPUB个人空间.g?5W'A'gqF[
T2

Execution PlanITPUB个人空间dic/v-w
----------------------------------------------------------
;UGB
TpA)C&w&i0   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=19)ITPUB个人空间c/K8\kV!l
d
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=ITPUB个人空间 I2sN-hOv5^
          19)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE) (Cost=1 CaITPUB个人空间Lr]0h2vX'i
          rd=1)

StatisticsITPUB个人空间4p/yd#S1d:kCI0i
----------------------------------------------------------ITPUB个人空间eoi!ik4X)lP
          0  recursive callsITPUB个人空间7JMoS c'{uI"K {/L
          0  db block gets
Kg C[/K3k0          4  consistent gets      
.QowHj0          0 physical reads   第二次读取 就不需要硬盘读取了,直接在data buffer中读了
1t uk~*v#m]'T0          0  redo size
Oj;G'|6yi| Z{
xi0        443  bytes sent via SQL*Net to client
P,zP
LT,yH Rd0        503  bytes received via SQL*Net from clientITPUB个人空间Oq9_g_1Dh
          2  SQL*Net roundtrips to/from client
s\ r&SPey N U0          0  sorts (memory)
B.bKVuNW
Pc0          0  sorts (disk)
.Uuk S_h;{6D \0          1  rows processed