这样通过统计信息比较执行效率行吗

Question

如题：以下是我的测试数据(200W)，由于执行计划比较多，就没全贴出来了！
--创建表和插入模拟数据
create table ta as
select level taskid,
       2 basictaskid,
       2 status 
  from dual
  connect by level <= 1000000;
insert into ta(taskid,basictaskid,status)
select level+1000000 taskid,
       3 basictaskid,
       3 status
  from dual 
  connect by level <= 1000000;insert into ta(taskid,basictaskid,status)
select level+2000000 taskid,
       1 basictaskid,
       1 status
  from dual 
  connect by level <= 1000;--1.status字段上不建索引
select /*+ index(m ix_x1)*/m.taskid,m.basictaskid,m.status
   from ta m
  where status = 1;
  
统计信息
----------------------------------------------------------
        193  recursive calls
          1  db block gets
       4827  consistent gets
       2211  physical reads
        176  redo size
      19935  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       1000  rows processed
        
--2.status字段上建有普通索引
create index ix_x1 on ta(status); select /*+ index(m ix_x1)*/m.taskid,m.basictaskid,m.status
   from ta m
  where status = 1;统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        226  consistent gets
          3  physical reads
          0  redo size
      26134  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
      --3.status字段上建有位图索引
--经常进行dml操作的表,不宜建位图索引.  
create bitmap index ix_x2 on ta(status); select /*+ index(m ix_x2)*/t.*
   from ta t 
  where t.status = 1;统计信息
----------------------------------------------------------
         36  recursive calls
          0  db block gets
       4773  consistent gets
          0  physical reads
          0  redo size
      19935  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
       --4.创建decode(t.status,1,1,2,2,4,4,null)函数索引
create index ixf_ix3 on ta(decode(status,1,1,2,2,4,4,null)); select /*+ index(m ixf_ix3)*/m.taskid,m.basictaskid,m.status
   from ta m
  where decode( m.status,1,1,2,2,4,4,null) = 1;统计信息
----------------------------------------------------------
        101  recursive calls
          0  db block gets
        246  consistent gets
          3  physical reads
          0  redo size
      19935  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
         
  从上面的统计信息来看，建普通索引的效率最高，请问：这样的测试的对的吗？
 

Answer 1

其实你后面三中测试：建普通索引、位图索引以及函数索引，三个的无别在于recursive calls
和consistent gets 的不同。
consistent gets 跟Oracle一致性读相关，如果你在读取的数据正在被其他事物或者session修改，那么此consistent gets从回滚段读取的就多。如普通的select语句、索引访问而引起的将数据读入到buffer中(也可能为physical read)或直接从buffer中读数据。
由于你说的ta表频繁做DML，而DML语句也能引起consistent gets，如update ta where status = 1；因为该语句需要找到需要被修改的所有数据块，在找数据块的过程中就会引起consistent gets。因此照成consistent gets不同。

Answer 2

1、你的测试方法不妥，因为第一次取出数据后，然后数据被写入buffer_cache，以后每次查询的物理读就为0了。所以如果严格的测试，应该每次测试完毕重启一下数据库。如果不重启数据库，可一试一下清除buffer_cache，使用命令：alter system flush buffer_cache;
2、从测试结果来看，普通索引效果好，因为选择的记录数与总记录数据之比趋近于0;