限制索引 (转)

 限制索引
   限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。后文的各小节将讨论一些常见的问题。
Oracle优化器在后台工作，选择并使用可能最有效的数据检索方法。例如，在许多情况下不需要指定WHERE子句，从而Oracle可以使用索引。如果查询索引列的MIN或MAX值，Oracle将从索引(而不是表)中检索该值。同样，如果对索引列执行COUNT函数，Oracle可以使用索引而不是该列。在下面的小节中，您将看到WHERE子句的逻辑阻止Oracle使用索引的情况。
2.3.1  使用不等于运算符(<>、!=)
索引只能用于查找表中已有的数据。每当在WHERE子句中使用不等于运算符时，都将无法使用所引用的列的索引。请考虑下文对CUSTOMERS表的查询，CUSTOMERS表中的CUST_RATING列有一个索引。下面的语句仍会执行一次全表扫描(因为大多数记录都可以被检索到)，即使列CUST_RATING上存在索引。
select       cust_id,   cust_name
from         customers
where        cust_rating < > 'aa';
当分析表时，Oracle收集表中数据分布的相关统计信息。通过使用这种分析，基于成本的优化器就可以决定在WHERE子句中对一些值使用索引，而对其他的值不使用索引。在应用程序开发和测试期间，应该使用具有代表性的行集，从而可以模拟产品环境中实际的数据值分布。
技巧：
通过使用CREATE INDEX命令的COMPUTE STATISTICS子句，可以在一个步骤中创建索引并分析它们。也可以从产品数据库中导入统计信息以测试执行路径

2.3.2  使用 IS NULL或IS NOT NULL
在WHERE子句中使用 IS NULL或IS NOT NULL同样会限制索引的使用，因为NULL值并没有被定义。数据库中没有值等于NULL值；甚至NULL也不等于NULL。
在SQL语句中使用NULL会有很多麻烦。如果被索引的列在某些行中存在NULL值，在索引中就不会有相应的条目(例外情况是位图索引，这是位图索引对于NULL搜索通常较为快速的原因)。一般情况下，下面的语句将造成执行全表扫描，即使Sal列被索引。
select       empno,  ename,  deptno
from          emp
where        sal is null;
如果要在上面的三列中禁用NULL值，可以在创建或修改表时使用NOT NULL。注意，如果表中已经包含数据，只有在表中每一行都有非NULL值或是使用ALTER TABLE命令的DEFAULT子句时，才可以为列设置NOT NULL属性。下面的程序清单显示了修改EMP表的Sal列以禁用NULL值：
alter  table  emp  modify (sal  not  null);
注意，如果想尝试在Sal列中插入一个NULL值，会返回一个错误信息。
技巧：
在创建表时对列指定NOT NULL后会禁用NULL值，而且可以避免与使用NULL值相关的性能问题。
下面的创建表语句为Deptno列提供了一个默认值。如果在执行INSERT操作时该列没有指定的值，就会使用默认值。如果指定了默认值，并且您确实需要使用NULL值，则需要在该列中插入NULL。
create table employee(
empl_id number(8) not null,
first_name varchar2(20) not null,
last_name varchar2(20) not null,
deptno number(4) default 10);

insert into employee(empl_id, first_name, last_name)  values (8100, 'REGINA', 'NIEMIEC');
1 row created.
select        *from          employee;
EMPL_ID  FIRST_NAME             LAST_NAME          DEPTNO
---------- -------------------- -------------------- ----------
8100   REGINA                  NIEMIEC                         10


insert into employeevalues (8200, 'RICH', 'NIEMIEC', NULL);
1 row created.


select       *from         employee;
EMPL_ID FIRST_NAME          LAST_NAME         DEPTNO
----------  -------------------- -------------------- ----------
8100   REGINA                   NIEMIEC                       10
8200   RICH                      NIEMIEC


技巧：
NULL值通常会限制索引。在创建表时对某一列指定NOT NULL或DEFAULT，对于避免可能出现的性能问题很有帮助。
2.3.3  使用函数
如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。一些常见的函数，如TRUNC、SUBSTR、TO_DATE、TO_CHAR和INSTR等，都能改变列的值。因此，无法使用已被函数引用的索引和列。下面的语句会执行一次全表扫描，即使hire_date列上存在索引(只要它不是基于函数的索引)。
select        empno, ename, deptno
from          emp
where         trunc(hiredate) = '01-MAY-01';
把上面的语句改成如下所示的语句，这样就可以通过索引进行查找。
select        empno, ename, deptno
from           emp
where         hiredate > '01-MAY-01'
and            hiredate < (TO_DATE('01-MAY-01') + 0.99999);
技巧：
通过改变所比较的列上的值，而不用改变列本身，就可以启用索引用。这样可避免全表扫描。

2.3.4  比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。Oracle并不会对那些不匹配的数据报错-- 事实正好相反。例如，Oracle可以隐式地把VARCHAR2列的数据转换成要被比较的数值型数据类型。考虑如下的示例，其中account_number就是一个VARCHAR2类型。
如果Account_Number列使用VARCHAR2数据类型，下面的语句将执行全表扫描，即便是索引account_number列：

select       bank_name, address, city, state, zip
from         banks
where        account_number = 990354;
Oracle可以自动把WHERE子句变成
to_number(account_number)=990354
这样就限制了索引的使用。这个查询的EXPLAIN PLAN仅显示通过"全表扫描"访问这个表(对编程人员来说通常都很迷惑)。对一些DBA或开发人员来说，这样的情况可能很少见，但在很多系统中，数字型值可以用零填充，然后指定为VARCHAR2类型。前面的语句可以改写成如下语句，这样可以正确地对这个字段使用单引号，以使用账号上的索引。select      bank_name, address, city, state, zip
from        banks
where       account_number = '000990354';
作为选择，可以定义Account_Number列使用NUMBER数据类型，前提是前置的0不是该列的关键信息。
技巧：
不匹配数据类型之间的比较会让Oracle自动限制索引的使用。即便对这个查询执行EXPLAIN PLAN也不能让您明白为什么做了一次"全表扫描"。只有了解关于数据类型的知识才能帮助您解决这个问题。

2.4  选择性
Oracle根据查询和数据，提供了多种方法来判断使用索引的价值。第一个方法就是判断索引中的唯一键或不同键的数量。可以对表或索引进行分析来确定不同键的数量。可以查询USER_INDEXES视图的Distinct_Keys列来研究分析的结果。比较一下唯一键的数量和表中的行数(如USER_INDEXES视图的Num_Rows列所示)，就可以判断索引的选择性。选择性越高，索引返回的行数就越少，该索引就越好。
技巧：
索引的选择性可以帮助基于成本的优化器来判断执行路径。索引的选择性越高，针对每个不同值返回的行数也越少。对于组合索引在索引中添加额外的列不会显著改善选择性，并且使用额外列的成本会超出收益。
查看更多： http://book.违规广告.com/art/200812/104308.htm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21340813/viewspace-615430/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21340813/viewspace-615430/