index full scan情况下10g和9i逻辑读差异的解释

index full scan情况下10g和9i逻辑读差异的解释
http://www.itpub.net/thread-1113449-1-1.html

具体帖子
http://www.itpub.net/thread-1113360-1-1.html

10g index full scan :

SQL> col name for a60
SQL> set linesize 300
SQL>select sid,value,name from v$sesstat a,v$statname b where a.statistic#=b.statistic#
and upper(b.name) like '%TABLE%' and sid=160;

SID VALUE NAME
---------- ---------- ------------------------------------------------------------
160 0 DBWR tablespace checkpoint buffers written
160 0 DBWR transaction table writes
160 3 physical reads direct temporary tablespace
160 0 physical writes direct temporary tablespace
160 0 transaction tables consistent reads - undo records applied
160 0 transaction tables consistent read rollbacks
160 0 auto extends on undo tablespace
160 219 table scans (short tables)
160 0 table scans (long tables)
160 0 table scans (rowid ranges)
160 0 table scans (cache partitions)

SID VALUE NAME
---------- ---------- ------------------------------------------------------------
160 0 table scans (direct read)
160 804544 table scan rows gotten
160 9477 table scan blocks gotten
160 68007 table fetch by rowid
160 62 table fetch continued row
160 0 table lookup prefetch client count

在session160上执行
select /*+ index(t) */ * from test t where t.object_name like '%TEST%';

结果返回80行

SQL> select count(*) from test;

COUNT(*)
----------
52119

SQL> select sid,value,name from v$sesstat a,v$statname b where a.statistic#=b.statistic#
and upper(b.name) like '%TABLE%' and sid=160;

SID VALUE NAME
---------- ---------- ------------------------------------------------------------
160 0 DBWR tablespace checkpoint buffers written
160 0 DBWR transaction table writes
160 3 physical reads direct temporary tablespace
160 0 physical writes direct temporary tablespace
160 0 transaction tables consistent reads - undo records applied
160 0 transaction tables consistent read rollbacks
160 0 auto extends on undo tablespace
160 219 table scans (short tables)
160 0 table scans (long tables)
160 0 table scans (rowid ranges)
160 0 table scans (cache partitions)

SID VALUE NAME
---------- ---------- ------------------------------------------------------------
160 0 table scans (direct read)
160 804544 table scan rows gotten
160 9477 table scan blocks gotten
160 68087 table fetch by rowid
160 62 table fetch continued row
160 0 table lookup prefetch client count

已选择17行。


table fetch by rowid只有80个，就是返回的结果

9i 环境下:


select sid,value,name from v$sesstat a,v$statname b where a.statistic#=b.statistic#
and upper(b.name) like '%TABLE%' and sid=9;


SQL> col name for a30
SQL> set linesize 300
SQL> select sid,value,name from v$sesstat a,v$statname b where a.statistic#=b.statistic#
2 and upper(b.name) like '%TABLE%' and sid=9;

SID VALUE NAME
---------- ---------- ------------------------------
9 0 DBWR transaction table writes
9 0 transaction tables consistent
reads - undo records applied

9 0 transaction tables consistent
read rollbacks

9 196 table scans (short tables)
9 5 table scans (long tables)
9 0 table scans (rowid ranges)
9 0 table scans (cache partitions)

SID VALUE NAME
---------- ---------- ------------------------------
9 0 table scans (direct read)
9 141855 table scan rows gotten
9 3213 table scan blocks gotten
9 221987 table fetch by rowid
9 39 table fetch continued row
9 0 table lookup prefetch client c
ount


已选择13行。

在sid为9的session上执行

SQL> select /*+ index(t) */ * from test t where t.object_name like '%TEST%';

这个实际上只返回9行

SQL> select count(*) from test;

COUNT(*)
----------
27190

SQL> select sid,value,name from v$sesstat a,v$statname b where a.statistic#=b.statistic#
2 and upper(b.name) like '%TABLE%' and sid=9;

SID VALUE NAME
---------- ---------- ------------------------------
9 0 DBWR transaction table writes
9 0 transaction tables consistent
reads - undo records applied

9 0 transaction tables consistent
read rollbacks

9 196 table scans (short tables)
9 5 table scans (long tables)
9 0 table scans (rowid ranges)
9 0 table scans (cache partitions)

SID VALUE NAME
---------- ---------- ------------------------------
9 0 table scans (direct read)
9 141855 table scan rows gotten
9 3213 table scan blocks gotten
9 249177 table fetch by rowid
9 39 table fetch continued row
9 0 table lookup prefetch client c
ount


已选择13行。

执行前后table fetch by rowid差距27190，就是test表的记录数

从以上的2个测试表明,10g在index full scan阶段就把不符合记录的数据先给过滤掉了,最后只有符合条件的rowid做回表,而9i下,oracle就比较啥了,index full scan的阶段实际上什么也没做,只是把所有的rowid都给得到,然后根据得到的rowid从表中取出数据,再过滤掉不符合条件的记录