索引分析和比较

转自：http://tb.blog.csdn.net/TrackBack.aspx?PostId=1729625

1、各种索引的创建方法
(1)*tree索引
create index indexname on tablename(columnname);

(2)反向索引
create index indexname on tablename(columnname) reverse;

(3)降序索引
create index indexname on tablename(columnname DESC);

(4)位图索引
create BITMAP index indexname on tablename(columnname);

(5)函数索引
create index indexname on tablename(functionname(columnname));

创建索引后要分析才能使用

analyze table test compute statistics  for table for all indexed columns for all indexes;
analyze index test validate structure;
select name,lf_rows from index_stats;用这条语句可以查询保存了多少条索引
analyze index test compute statistics;

从字面理解validate structure 主要在于校验对象的有效性. compute statistics在于统计相关的信息..

查询索引
select index_name,index_type from user_indexes where table_name='TEST';

2、打开autotrace功能
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql和$ORACLE_HOME/sqlplus/admin/plustrce.sql
然后给相关用户授予plustrace角色，然后这些用户就可以使用autotrace功能了

3、无效索引
(1)类型不匹配
create table test(a varchar(2),b number);
insert into test values('1',1);
create index test_index on test(a);
analyze table test compute statistics  for table for all indexed columns for all indexes;
set autotrace on;

类型匹配的情况
select /*+ RULE */ *  from test where a='1';

A           B
-- ----------
1           1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE) 使用了索引

类型不匹配的情况
select /*+ RULE */ *  from test where a=1;

A           B
-- ----------
1           1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TEST' 选择了全表扫描

(2)条件包含函数但没有创建函数索引
alter system set QUERY_REWRITE_ENABLED=true;
alter system set query_rewrite_integrity=enforced;

insert into test values('a',2);
select /*+ RULE */ *  from test where upper(a) = 'A';
A           B
-- ----------
a           2

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

由于没有创建函数索引，所以选择全表扫描

create index test_index_fun on test(upper(a));
analyze table test compute statistics  for table for all indexed columns for all indexes;

select /*+ RULE */ *  from test where upper(a) = 'A';
A                                                           B
-------------------------------------------------- ----------
a                                                           2
a                                                           3
a                                                           4

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

虽然创建了函数索引，但由于工作于RBO模式，所以函数索引没用，选择了全表扫描

select * from test where upper(a) = 'A';
A                                                           B
-------------------------------------------------- ----------
a                                                           2
a                                                           3
a                                                           4

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=9)

   2    1     INDEX (RANGE SCAN) OF 'TEST_INDEX_FUN' (NON-UNIQUE) (Cos
          t=1 Card=1)

当函数索引工作于CBO模式下，选择了基于函数的索引，上面创建的索引函数TEST_INDEX_FUN已经用到

(3)符合索引中的前导列没有被作为查询条件
create index test_index_com on test(a,b);
select /*+ RULE */ *  from test where a = '1';
A           B
-- ----------
1           1

前导列a作为了查询条件，但由于之前创建了a的索引，所以使用了TEST_INDEX而没有使用test_index_com
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)

select /*+ RULE */ *  from test where b = '1';

A           B
-- ----------
1           1
2           1
3           1
4           1

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

前导列a没有作为查询条件，所以选择全部扫描

select /*+ RULE */ *  from test where b = '1' and a= '1';

A           B
-- ----------
1           1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   INDEX (RANGE SCAN) OF 'TEST_INDEX_COM' (NON-UNIQUE)
前导列a作为了查询条件，使用了索引

(4)CBO模式下选择的行数比例过大，优化器选择全表扫描

declare
       i number;
       j number;
begin
for i in 1 .. 10 loop
for j in 1 .. 10000 loop
insert into test values(to_char(j),i);
end loop;
end loop;
end;
/

declare i number;
begin
for i in 1 .. 100 loop
insert into test values(to_char(i),i);
end loop;
end;
/

SQL> select count(*) from test;

  COUNT(*)
----------
    200000

select  * from test where a = '1';
已选择10000行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=9333 Bytes=7
          4664)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=27 Card=9333 Bytes=746
          64)

比例过大，选择全表扫描

select * from test where a = '99';

已选择10行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=16)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
          es=16)

   2    1     INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE) (Cost=1
          Card=2)

比例小，选择索引

select  /*+ RULE */ * from test where a = '1';

已选择10000行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)

如果指定为RBO优化器，肯定就用索引了

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

(6)索引条件中使用了<>、!=、not\not in、not like等操作符，导致查询不使用索引
先做一个测试在一个表中插入130万条数据，其中不等于1的数据有30万条，以下是几种语句执行的结果
 序号 语句                                                                 时间          代价
 1    select * from test where b<>1;                                       00: 00: 03.04 398
 2    select * from test where b not like 1;                               00: 00: 03.03 398
 3    select * from test where b !=1;                                      00: 00: 03.01 398
 4    select * from test where b not in(1);                                00: 00: 03.00 398
 5    select * from test where b<1 union select * from test where b>1;     00: 00: 03.01 264
 6    select * from test where b<1 union all select * from test where b>1; 00: 00: 02.09 132
 7    select * from test where b<1 or b>1;                                 00: 00: 02.08 96
从以上可以看出最优化的语句是7，在查询过程中使用索引的有5、6、7
所以，如果建立了索引，在语句中尽量不要使用<>、!=、not、not in、not like操作，如果非要使用，请尽量用or和union操作替换

(7)索引对空值的影响

我们首先做一些测试数据：

SQL> create table t(x int, y int);

请注意，这里我对表t做了一个唯一（联合）索引：

SQL> create unique index t_idx on t(x,y);
SQL> insert into t values(1,1);
SQL> insert into t values(1,NULL);
SQL> insert into t values(NULL,1);
SQL> insert into t values(NULL,NULL);
SQL> commit;

下面我们分析一下索引：

SQL> analyze index t_idx validate structure;

SQL> select name,lf_rows from index_stats;

NAME                              LF_ROWS

------------------------------ ----------

T_IDX                                   3

然后，我们就可以看到，当前的索引中仅仅保存了3行数据。

请注意，上面我们插入并提交了四行数据。

所以，这里就有一个结论：

Oracle的索引不保存该索引包含的列中全部为空的行。
这同时也带来个好处，但当一个表中的某一列大部分为空值，至少90%以上是空值的时候，就可以为该列建立索引。

比如该表为t，该列为x
select * from t where x is null;
此时会选择全表扫描

select * from t where x=1;
此时就会使用索引，而且索引中不保存值为空的行，所以索引中只有10%左右的行，因此在这10%的行中找出x=1的行比在全表中找出x=1的行要快的多

我们继续插入数据，现在再插入几行全部为空的行：

SQL> insert into t values(NULL,NULL);

SQL> insert into t values(NULL,NULL);

我们看到这样的插入，居然没有违反前面我们设定的唯一约束（unique on t(x,y)），

所以，这里我们又得出一个结论：

Oracle认为 NULL<>NULL ，进而 (NULL,NULL)<>(NULL,NULL)

换句话说，Oracle认为空值（NULL）不等于任何值，包括空值也不等于空值。

我们看到下面的插入会违反唯一约束(DEMO.T_IDX)，这个很好理解了，因为它不是全部为空的值，即它不是（NULL,NULL），只有全部为空的行才被认为是不同的行：

SQL> insert into t values(1,null);

ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)

SQL> insert into t values(null,1);

ORA-00001: 违反唯一约束条件 (DEMO.T_IDX)

 

SQL>

 

请看下面的例子:

SQL> select x,y,count(*) from t group by x,y;

 

    X        Y   COUNT(*)

----- -------- ----------

                        3

             1          1

    1                   1

    1        1          1

Executed in 0.03 seconds

 

SQL> select x,y,count(*) from t where x is null and y is null group by x,y;

 

   X       Y   COUNT(*)

---- ------- ----------

                      3

 

Executed in 0.01 seconds

 

SQL>

SQL> select x,y,count(*) from t group by x,y having count(*)>1;

 

     X                    Y   COUNT(*)

------ -------------------- ----------

                                     3

 

Executed in 0.02 seconds

SQL>

可以看见，完全为空的行有三行，这里我们又可以得出一个结论：

oracle在group by子句中认为完全为空的行是相同的行

换句话说，在group by子句中，oracle认为(NULL,NULL)=(NULL,NULL)

SQL> select * from t where x is null;

         X          Y
---------- ----------
                    1

 

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=4 Bytes=8)

 

SQL> select * from t where x=1;

         X          Y
---------- ----------
         1          1
         1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=4)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (UNIQUE) (Cost=1 Card=2 Byte
          s=4)

 

从以上可以看出，在使用IS NULL 和 IS NOT NULL条件的时候，Oracle不使用索引

那么我们如何使用空值的比较条件呢？

首先，尽量不在前导列上使用空值，其次我们在创建表的时候，为每个列都指定为非空约束（NOT NULL），并且在必要的列上使用default值

 

8、不要为所有的列建立索引

我们知道，建立索引是为了提高查询的效率，但是同时也应该注意到，索引增加了对DML操作（insert, update, delete）的代价，而且，一给中的索引如果太多，那么多数的索引是根本不会被使用到的，而另一方面我们维护这些不被使用的所以还要大幅度降低系统的性能。所以，索引不是越多越好，而是要恰到好处的使用。

 

比如说，有些列由于使用了函数，我们要使用已有的索引（如一些复合索引）是不可能的，那么就必须建立单独的函数索引，如果说这个函数索引很少会被应用（仅仅在几个特别的sql中会用到），我们就可以尝试改写查询，而不去建立和维护那个函数索引，例如：

 

1，trunc函数

SQL> select empno,ename,deptno from emp where trunc(hiredate)='2004-01-01';

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'EMP'

将上面的查询转换为：

SQL> select empno,ename,deptno from emp

  2  where hiredate >= to_date('2004-01-01','yyyy-mm-dd')

  3  and hiredate<to_date('2004-01-01','yyyy-mm-dd')+0.999;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

   2    1     INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE)

2，to_char函数

SQL> select empno,ename,deptno from emp

  2  where to_char(hiredate,'yyyy-mm-dd')='2003-09-05';

 
Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'EMP'

 

SQL> select empno,ename,deptno from emp

  2  where hiredate=to_date('2003-09-05','yyyy-mm-dd');

 
Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

   2    1     INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE)

 
3，substr函数

SQL> select dname from dept where substr(dname,1,3)='abc';

 
Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'DEPT'

 

SQL>  select dname from dept where dname like 'abc%';

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   INDEX (RANGE SCAN) OF 'DEPT_ID1' (NON-UNIQUE)

 通常，为了均衡查询的效率和DML的效率，我们要仔细的分析应用，找出来出现频率相对较多、字段内容较少（比如varchar2(1000)就不适合建立索引，而varchar2(10)相对来说就适合建立索引）的列，合理的建立索引，比如有时候我们希望建立复合索引，有时候我们更希望建立单键索引。