ORACLE索引

  • 经常一起使用多个字段检索记录,组合索引比单索引更有效
  • 把最常用的列放在最前面,例:dx_groupid_serv_id(groupid,serv_id),在where条件中使用groupid或groupid,serv_id,查询将使用索引,若仅用到serv_id字段,则索引无效
  • Date型列一般适合基于函数的索引
  • 列中有许多空值,不适合建立索引
  • 一个表可以有几百个索引(你会这样做吗?),但是对于频繁插入和更新表,索引越多系统CPU,I/O负担就越重;建议每张表不超过5个索引
  • 创建索引时指定表空间,特别是在建立主键时,应明确指定表空间
  • 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.如SELECT LODGING FROM LODGING WHERE MANAGER LIKE ‘%HANMAN';在这种情况下,ORACLE将使用全表扫描


oracle的索引陷阱
一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 
oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。 
下面是一些常见的索引限制问题。
 
1、使用不等于操作符(<>, !=)
下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描 
select * from dept where staff_num <> 1000; 
但是开发中的确需要这样的查询,难道没有解决问题的办法了吗? 
有! 
通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。 
select * from dept shere staff_num < 1000 or dept_id > 1000; 
 
2、使用 is null 或 is not null
使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。 
解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)
 
3、使用函数
如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引: 
select * from staff where trunc(birthdate) = '01-MAY-82'; 
但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。 
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 
 
4、比较不匹配的数据类型
比较不匹配的数据类型也是难于发现的性能问题之一。
下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。 
select * from dept where dept_id = 900198; 
这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。
把SQL语句改为如下形式就可以使用索引 
select * from dept where dept_id = '900198';


Oracle 为什么不使用索引
  
  。检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中,这是“执行计划”能用到相关索引的必要条件。
  
  。看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join(SMJ)、Hash Join(HJ)和Nested Loop Join(NL)。在两张表连接,且内表的目标列上建有索引时,只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程。HJ由于须做HASH运算,索引的存在对数据查询速度几乎没有影响。
  
  。看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引,表dept的列deptno上无索引,WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时,emp做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描,emp.deptno上的索引显然是用不上,最多在其上做索引全扫描或索引快速全扫描。
  
  。是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过,可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析,否则可能导致死锁,或系统性能下降。
  
  。索引列是否函数的参数。如是,索引在查询时用不上。
  
  。是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致上一种现象的发生。
  
  。是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析,可用SQL语句“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映实际的统计数据,才有可能做出正确的选择。
  
  。索引列的选择性不高。   我们假设典型情况,有表emp,共有一百万行数据,但其中的emp.deptno列,数据只有4种不同的值,如10、20、30、40。虽然emp数据行有很多,ORACLE缺省认定表中列的值是在所有数据行均匀分布的,也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10,利用deptno列上的索引进行数据搜索效率,往往不比全表扫描的高。
  
  。索引列值是否可为空(NULL)。如果索引列值可以是空值,在SQL语句中那些要返回NULL值的操作,将不会用到索引,如COUNT(*),而是用全表扫描。这是因为索引中存储值不能为全空。
  
  。看是否有用到并行查询(PQO)。并行查询将不会用到索引。
  
  。如果从以上几个方面都查不出原因的话,我们只好用采用在语句中加hint的方式强制ORACLE使用最优的“执行计划”。  hint采用注释的方式,有行注释和段注释两种方式。  如我们想要用到A表的IND_COL1索引的话,可采用以下方式:  “SELECT /*+ INDEX(A IND_COL1)*/ * FROM A WHERE COL1 = XXX;"


相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页