关于index range scans

wwllzpz

通过index range scans访问的表可以通过按索引重建表来提高效率, 是因为根据索引的叶子block中数据去访问表，和 key 大小顺序一致,还是重建表以后访问的数据块更少?  它的原理是什么?

biti_rainy

最初由 xzh2000 发布
[B]

INDEX FFS是说如果可以从INDEX上获取某次访问所需要的数据，
那就执行INDEX FFS，不需要去访问表；如果不可以从INDEX上获
取某次访问的数据，它可能会执行INDEX FULL SCAN，然后再通过ROWID去访问DATA BLOCK [/B]

sorry ,我在这里错把 index  fast  full  scan   当成  index  full  scan 了。


但是 按照索引来重建表就是指的 按照索引顺序重新建立表，和 IOT 没有必然联系。IOT 并不是随便使用的，不同版本有不同的不少限制条件。如果你本来就有个 主键，又如何根据新的索引来做 IOT ?



如果你要问重建了表过了一段时间之后顺序又不好了怎么办，那可以考虑再重建。


index  fast  full  scan 只访问索引，所以和表无关。

biti_rainy

最初由 wwllzpz 发布
[B]通过index range scans访问的表可以通过按索引重建表来提高效率, 是因为根据索引的叶子block中数据去访问表，和 key 大小顺序一致,还是重建表以后访问的数据块更少?  它的原理是什么? [/B]

这个问题和 碎片无关。即使根本没有碎片也成。

这样做后在复杂环境下可能会大大减少 IO ，在 920 会大大减少  逻辑读，你做个测试就知道了。

wwllzpz

最初由 biti_rainy 发布
[B]

这个问题和 碎片无关。即使根本没有碎片也成。

这样做后在复杂环境下可能会大大减少 IO ，在 920 会大大减少  逻辑读，你做个测试就知道了。 [/B]

  测试已经做过了,确实可以减少逻辑读,但是这样重建表可以减少IO的原理我看了一些资料还是不很清楚.麻烦biti_rainy 解释一下?

biti_rainy

最初由 wwllzpz 发布
[B]
  测试已经做过了,确实可以减少逻辑读,但是这样重建表可以减少IO的原理我看了一些资料还是不很清楚.麻烦biti_rainy 解释一下? [/B]

1: 如果你只读一部分数据，假设20% ，如果表数据顺序混乱，实际上可能把整个表都读进来了。如果表顺序和索引一致，则只需要读进 20%的表的block就够了。这是简单情况。


2：复杂情况下，顺序混乱的时候 block  可能在整个查询的不同时间点多次反复访问，当再次要访问这个块的时候说不定已经被换出去了，或者被修改过了，那代价更大。  而如果顺序一样，对同一个block的访问集中在一段连续的很短的时间内，变数少，不会对同一个block产生多次 IO

rchsh

根据索引重建表以后，这样相临的数据就集中在很少的数据块中了，所以可以减少数据块的读取

husthxd

最初由 rchsh 发布
[B]根据索引重建表以后，这样相临的数据就集中在很少的数据块中了，所以可以减少数据块的读取 [/B]

从表统计信息中的cluster_factor可以判断是否有很好的聚集性.
btw:
记得有个文档上说ffs比fs快的一个原因是使用了多块读.
不清楚索引的多块读是否跟表block的多块读的机理是否一致?

biti_rainy

最初由 husthxd 发布
[B]

从表统计信息中的cluster_factor可以判断是否有很好的聚集性.
btw:
记得有个文档上说ffs比fs快的一个原因是使用了多块读.
不清楚索引的多块读是否跟表block的多块读的机理是否一致? [/B]

FFS和FTS是一样的，所以这样出来的数据也并不是有序的。只是索引比表小，快一些而已。


如果你创建一个一两个数字字段的表，走FFS，反而比FTS慢了，因为索引比表还大  

husthxd

最初由 biti_rainy 发布
[B]



FFS和FTS是一样的，所以这样出来的数据也并不是有序的。只是索引比表小，快一些而已。


如果你创建一个一两个数字字段的表，走FFS，反而比FTS慢了，因为索引比表还大   [/B]

昏了,应该是如果在字段有not null约束的话.可以使用ffs.
如果字段较小的话,比较ffs/fts的统计信息,确实ffs的效率比fts的效率还要低.


SQL>
SQL> drop table t1
  2  /

表已丢弃。

已用时间:  00: 00: 00.02
SQL> create table t1 (n1 varchar2(10))
  2  /

表已创建。

已用时间:  00: 00: 00.00
SQL> declare
  2  begin
  3    for i in 1..100000 loop
  4      insert into t1 values(to_char(i));
  5    end loop;
  6    commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 05.06
SQL>
SQL> create index idx_t1_1 on t1(n1)
  2  /

索引已创建。

已用时间:  00: 00: 00.06
SQL> analyze table t1 compute statistics for table for all indexes
  2  /

表已分析。

已用时间:  00: 00: 01.03
SQL>
SQL> drop table t2
  2  /

表已丢弃。

已用时间:  00: 00: 00.05
SQL> create table t2 (c1 varchar2(10),c2 char(10),c3 char(10),c4 char(10),c5 char(10),c6 char(10))
  2  /

表已创建。

已用时间:  00: 00: 00.00
SQL> declare
  2  begin
  3    for i in 1..100000 loop
  4      insert into t2 values(to_char(i),'test','test','test','test','test');
  5    end loop;
  6    commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 07.02
SQL> create index idx_t2_1 on t2(c1)
  2  /

索引已创建。

已用时间:  00: 00: 00.07
SQL> analyze table t2 compute statistics for table for all indexes
  2  /

表已分析。

已用时间:  00: 00: 01.03
SQL>
SQL> set autotrace traceonly
SQL> set timing on
SQL>
SQL> select count(*) from t1
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=100000)                 




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
        156  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        208  bytes sent via SQL*Net to client                                   
        275  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 /*+index_ffs(t1)*/ count(*) from t1
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=100000)                 




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
        156  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        209  bytes sent via SQL*Net to client                                   
        275  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 count(*) from t2
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=89 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=89 Card=100000)                 




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
        915  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        209  bytes sent via SQL*Net to client                                   
        275  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 /*+index_ffs(t2 idx_t2_1)*/ count(*) from t2
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=89 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=89 Card=100000)                 




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
        915  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        209  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> drop index idx_t1_1
  2  /

索引已丢弃。

已用时间:  00: 00: 00.01
SQL> alter table t1 add constraint pk_t1 primary key(n1)
  2  /

表已更改。

已用时间:  00: 00: 00.07
SQL> analyze table t1 compute statistics for table for all indexes
  2  /

表已分析。

已用时间:  00: 00: 01.02
SQL> drop index idx_t2_1
  2  /

索引已丢弃。

已用时间:  00: 00: 00.01
SQL> alter table t2 add constraint pk_t2 primary key(c1)
  2  /

表已更改。

已用时间:  00: 00: 00.07
SQL> analyze table t2 compute statistics for table for all indexes
  2  /

表已分析。

已用时间:  00: 00: 01.03
SQL>
SQL> select /*+index_ffs(t1)*/ count(*) from t1
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     INDEX (FAST FULL SCAN) OF 'PK_T1' (UNIQUE) (Cost=23 Card         
          =100000)                                                              
                                                                                




Statistics
----------------------------------------------------------                     
         39  recursive calls                                                   
          0  db block gets                                                      
        238  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        208  bytes sent via SQL*Net to client                                   
        275  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 /*+full(t1)*/ count(*) from t1
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=100000)                 




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
        156  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        209  bytes sent via SQL*Net to client                                   
        275  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 /*+index_ffs(t2)*/count(*) from t2
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     INDEX (FAST FULL SCAN) OF 'PK_T2' (UNIQUE) (Cost=23 Card         
          =100000)                                                              
                                                                                




Statistics
----------------------------------------------------------                     
         39  recursive calls                                                   
          0  db block gets                                                      
        237  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        209  bytes sent via SQL*Net to client                                   
        275  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 /*+full(t2)*/ count(*) from t2
  2  /

已用时间:  00: 00: 01.03

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=89 Card=1)                    
   1    0   SORT (AGGREGATE)                                                   
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=89 Card=100000)                 




Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                      
        915  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        209  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed                                                     

SQL>
SQL> spool off

biti_rainy

cbo下，如果索引字段是 not null自然也可以了，和是否 pk没关系。

如果允许为null 当然不可以了！这不显然么，阐述过多次了。