索引什么时候不工作

首先要声明两个知识点：

 

（1）RBO&CBO。

 

Oracle

有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.

 

（2）AUTOTRACE。

 

要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：

 

①

由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。

 

② AUTOTRACE

可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。

 

③ AUTOTRACE

的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

 

    SQL> set autotrace on
SQL> select * from test;
         A
----------
         1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
rows processed

 

 

SQL> set autotrace traceonly
SQL> select * from test.test;

 

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

 

 

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

 

 

Hints

是Oracle提供的一个辅助用法，按字面理解就是‘提示’的意思，确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。使用方法如下：

 

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

 

 

具体可参考Oracle SQL Reference。

有了前面这些知识点，接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。

 

（1）类型不匹配时。

 

SQL> create table test.testindex (a varchar(2),b number);
表已创建。
SQL> create index ind_cola on test.testindex(a);
索引已创建。
SQL> insert into test.testindex values('1',1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> analyze table test.testindex compute statistics for all indexes;
表已分析。
SQL> set autotrace on;

 

 

SQL> select /*+RULE */* FROM test.testindex where a='1';(使用基于rule的优化器，数据类型匹配的情况下)
A           B
-- ----------
1           1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'
   2    1     INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）
――――――――――――――――――――――――――――――――――
SQL> select /*+RULE */* FROM test.testindex where a=1;（数据类型不匹配的情况）
A           B
-- ----------
1           1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择了全表扫描）

（2）条件列包含函数但没有创建函数索引。

 

SQL> select /*+ RULE */*  FROM test.testindex where upper(a)= 'A';（使用了函数upper()在列a上）;
A           B
-- ----------
a           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择全表扫描）
----------------------------------------------------------
创建基于函数的索引
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已创建。
SQL> insert into testindex values('a',2);
已创建1行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/*  FROM test.testindex where upper(a)='A';
A           B
-- ----------
a           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'
(在RULE优化器下忽略了函数索引选择了全表扫描)
-----------------------------------------------------------
SQL> select *  FROM test.testindex where upper(a)
='A';
A           B
-- ----------
a           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
          1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
          d=1)(CBO优化器使用了ind_fun索引)

（3）复合索引中的前导列没有被作为查询条件。

 

创建一个复合索引
SQL> create index ind_com on test.testindex(a,b);
索引已创建。
SQL> select /*+ RULE*/* from test.testindex where a='1';
A           B
-- ----------
1           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com)
SQL> select /*+ RULE*/* from test.testindex where b=1;
未选定行
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'(条件列表不包括前导列是选择全表扫描)
-----------------------------------------------------------

 

 

（4）CBO模式下选择的行数比例过大，优化器采取了全表扫描。

SQL> select * from test.testindex where a='1';
A           B
-- ----------
1           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5)
（表一共2行，选择比例为50%,所以优化器选择了全表扫描）
――――――――――――――――――――――――――――――――――
下面增加表行数
SQL> declare i number;
  2  begin
  3  for i in 1 .. 100 loop
  4  insert into test.testindex values (to_char(i),i);
  5  end loop;
  6  end;
  7  /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
  COUNT(*)
----------
102
SQL> select * from test.testindex where a='1';
A             B
---- ----------
1             1
1             2
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1    0   INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
（表一共102行，选择比例为2/102=2%,所以优化器选择了索引扫描）

 

（5）CBO模式下表很久没分析，表的增长明显，优化器采取了全表扫描。

 

SQL> select * from test.testindex where a like '1%';
A             B
---- ----------
1             2
1             1
10           10
11           11
12           12
13           13
14           14
15           15
16           16
17           17
18           18
19           19
100         100
已选择13行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
   1   0  TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共102行，选择比例为13/102>10%,优化器选择了全表扫描)
――――――――――――――――――――――――――――――――――
增加表行数
SQL> declare i number;
  2  begin
  3  for i in 200 .. 1000 loop
  4  insert into test.testindex values (to_char(i),i);
  5  end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
  COUNT(*)
----------
903
SQL> select * from test.testindex where a like '1%';
A             B
----  ----------
1             2
1             1
10           10
11           11
12           12
13           13
14           14
15           15
16           16
17           17
18           18
19           19
100          100
1000         1000
已选择14行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
   1  0  TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
  (表一共903行，选择比例为14/903<5%,优化器选择了全表扫描,选择路径是错误的)
―――――――――――――――――――――――――――――
给表做分析

 

SQL> analyze table test.testindex compute statistics for table for all indexed c
olumns for all indexes;
表已分析。
SQL> select * from test.testindex where a like '1%';
A             B
---- ----------
1             2
1             1
10           10
100         100
1000       1000
11           11
12           12
13           13
14           14
15           15
16           16
17           17
18           18
19           19
已选择14行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)
   1   0  TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card=
          24 Bytes=120)
   2  1  INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca
          rd=24)
（经过分析后优化器选择了正确的路径，使用了ind_cola索引）