Oracle建⽴索引及利⽤索引的SQL语句优化
数据库索引:
索引有单列索引
复合索引之说
如何某表的某个字段有主键约束和唯⼀性约束，则Oracle 则会⾃动在相应的约束列上建议唯⼀索引。数据库索引主要进⾏提⾼访问速度。
建设原则:
　1、索引应该经常建在Where ⼦句经常⽤到的列上。如果某个⼤表经常使⽤某个字段进⾏查询，并且检索⾏数⼩于总表⾏数的5%。则应该考虑。
　2、对于两表连接的字段，应该建⽴索引。如果经常在某表的⼀个字段进⾏Order By 则也经过进⾏索引。
　3、不应该在⼩表上建设索引。
优缺点:
　1、索引主要进⾏提⾼数据的查询速度。 当进⾏DML时，会更新索引。因此索引越多，则DML越慢，其需要维护索引。 因此在创建索引及DML需要权衡。
创建索引:
　单⼀索引:Create Index <Index-Name> On <Table_Name>(Column_Name);
　复合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建⽴索引。
  select * from emp where deptno=66 and job='sals' ->⾛索引。
  select * from emp where deptno=66 OR job='sals' ->将进⾏全表扫描。不⾛索引
  select * from emp where deptno=66 ->⾛索引。
  select * from emp where job='sals' ->进⾏全表扫描、不⾛索引。
  如果在where ⼦句中有OR 操作符或单独引⽤Job 列(索引列的后⾯列) 则将不会⾛索引，将会进⾏全
表扫描。
Sql 优化:
当Oracle数据库拿到SQL语句时，其会根据查询优化器分析该语句，并根据分析结果⽣成查询执⾏计划。数据库优化sql语句
也就是说，数据库是执⾏的查询计划，⽽不是Sql语句。
查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。
其中基于规则的查询优化器在10g版本中消失。
对于规则查询，其最后查询的是全表扫描。⽽CBO则会根据统计信息进⾏最后的选择。
1、先执⾏From ->Where ->Group By->聚集函数->having->计算表达式->select 字段->Order By
2、执⾏From 字句是从右往左进⾏执⾏。因此必须选择记录条数最少的表放在右边。这是为什么呢？ 
3、对于Where字句其执⾏顺序是从后向前执⾏、因此可以过滤最⼤数量记录的条件必须写在Where⼦
句的末尾，⽽对于多表之间的连接，则写在之前。
因为这样进⾏连接时，可以去掉⼤多不重复的项。 
4. SELECT⼦句中避免使⽤(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个⼯作是通过查询数据字典完成的, 这意味着将耗费更多的时间
5、索引失效的情况:
　① Not Null/Null 如果某列建⽴索引，当进⾏Select * from emp where depto is not null/is null。 则会是索引失效。
　② 索引列上不要使⽤函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC'
或者SELECT Col FROM tbl WHERE name LIKE '%ABC%' ⽽SELECT Col FROM tbl WHERE name LIKE 'ABC%' 会使⽤索引。
　③ 索引列上不能进⾏计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效，应该改成
SELECT Col FROM tbl WHERE col > 10 * 10
　④ 索引列上不要使⽤NOT （ != 、 <> ）如:SELECT Col FROM tbl WHERE col ! = 10
应该 改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 。
6、⽤UNION替换OR(适⽤于索引列)
  union：是将两个查询的结果集进⾏追加在⼀起，它不会引起列的变化。 由于是追加操作，需要两个结果集的列数应该是相关的，
并且相应列的数据类型也应该相当的。union 返回两个结果集，同时将两个结果集重复的项进⾏消除。 如果不进⾏消除，⽤UNOIN ALL.
通常情况下, ⽤UNION替换WHERE⼦句中的OR将会起到较好的效果. 对索引列使⽤OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效.
如果有column没有被索引, 查询效率可能会因为你没有选择OR⽽降低. 在下⾯的例⼦中, LOC_ID 和REGION上都建有索引.
  ⾼效:
  SELECT LOC_ID , LOC_DESC , REGION
  FROM LOCATION
  WHERE LOC_ID = 10
  UNION
  SELECT LOC_ID , LOC_DESC , REGION
  FROM LOCATION
  WHERE REGION = “MELBOURNE”
  低效:
  SELECT LOC_ID , LOC_DESC , REGION
  FROM LOCATION
  WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
  如果你坚持要⽤OR, 那就需要返回记录最少的索引列写在最前⾯.
7. ⽤EXISTS替代IN、⽤NOT EXISTS替代NOT IN
在许多基于基础表的查询中, 为了满⾜⼀个条件, 往往需要对另⼀个表进⾏联接. 在这种情况下, 使⽤EXISTS(或NOT EXISTS)通常将提⾼查询的效率.
在⼦查询中, NOT IN⼦句将执⾏⼀个内部的排序和合并. ⽆论在哪种情况下, NOT IN都是最低效的(因为它对⼦查询中的表执⾏了⼀个全表遍历).
为了避免使⽤NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例⼦：
⾼效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
低效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC =
‘MELB’)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。