SQL语句索引优化

先通过一个实验探讨一下索引的问题

创建测试环境

create table test01(c1 number,c2 number);

declare i number:=1;
begin
  while i<=100000 loop
    insert into test01(c1,c2) select i,dbms_random.value(1,100000) from dual;
    if mod(i,1000)=0 then 
      commit;
    end if;
    i:=i+1;
  end loop;
end;

create index idx_test01_c1 on test01(c1);

select * from test01 where c1 between 10000 and 20000;

执行计划：

可以看出此时走的是索引（INDEX RANGE SCAN），增加数据量再查：

select * from test01 where c1 between 10000 and 30000；

执行计划：

还是走的索引，继续增加：

select * from test01 where c1 between 10000 and 40000;

执行计划：

此时可以看出，虽然查询条件字段上有索引，但查询并没有走索引，这是为什么?

　　先来学习一下基础知识。因为oracle内部优化器在解析sql语句执行时，有基于规则（RBO:Rule Based Optimization）和基于成本（CBO: Cost Based Optimization）两种方式来选择sql语句的执行路径。

　　基于规则：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则，对数据是不敏感的。它只借助少量的信息来决定一个sql语句的执行计划。（10G之后不再支持）

　　基于成本：通过代价引擎来估计每个执行计划所需的代价，该代价将每个执行计划所耗费的资源进行量化，CBO根据这个代价选择出最优的执行计划。包括：I/O代价、CPU代价、NETWORK代价。

　　上面的实验就是一个很好的例子，test01.c1列上存在索引，但返回结果集数据量发生变化时，执行路径并不相同。在第三次，数据量变成3W条时，执行计划选择了全表扫描（TABLE ACCESS FULL），因为oracle认为取3W条数据时走全表扫描比走索引代价更低，所以当条件不同、数据量不同时sql语句的执行计划也不尽相同。

      另外，查询走索引也不一定能加快查询速度。

　　强制索引扫描：

SQL> set autotrace traceonly;
SQL> alter system flush buffer_cache;
SQL> select /*+INDEX(TEST01 IDX_TEST01_C1) */ * from test01 where c1 between 10000 and 100000;


跟踪结果：
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      12561  consistent gets
    601  physical reads
      0  redo size
    3994849  bytes sent via SQL*Net to client
      66523  bytes received via SQL*Net from client
       6002  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      90001  rows processed

强制全表扫描：

SQL> alter system flush buffer_cache;
SQL> select /*+FULL(TEST01) */ * from test01 where c1 between 10000 and 100000;

跟踪结果：
Statistics
----------------------------------------------------------
      0  recursive calls
      1  db block gets
       6453  consistent gets
    475  physical reads
      0  redo size
    3634901  bytes sent via SQL*Net to client
      66523  bytes received via SQL*Net from client
       6002  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      90001  rows processed

　　根据两个语句的执行计划来看，后者的物理读和一致性读的次数较少，表示更快的sql执行速度，这又是为何？索引范围扫描是串行单块读，全表扫描则是多块读（一个cache buffer chains可加载多个数据块），所以后者的减少了I/O次数，提升了查询效率。