关闭

Oracle 建立索引及利用索引的SQL语句优化

6038人阅读 评论(0) 收藏 举报
分类:

数据库索引:

索引有单列索引
复合索引之说


如何某表的某个字段有主键约束和唯一性约束,则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语句。
查询优化器有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’)


原创文章地址:http://www.cnblogs.com/tianmingt/articles/4444885.html

SQL 语句的执行顺序:http://wang286480403.iteye.com/blog/1899915

0
0
查看评论

SQLServer 语句-创建索引

SQLServer 语句-创建索引
  • a9529lty
  • a9529lty
  • 2010-12-17 14:04
  • 50002

SQL创建表索引语句

 SQL创建表索引语句:    语法:     use 数据库名     go     if exists(select name from sysindexes w...
  • john320
  • john320
  • 2015-06-08 09:03
  • 2091

SQL Server 创建和使用索引 (转载)

创建索引: (1)在SQL Server Management Studio中,选择并右击要创建索引的表,从弹出菜单中选择“设计”,打开表设计器。右键单击表设计器,从弹出菜单中选择“索引/键”命令,打开“索引/键”对话框。对话框中列出了已经存在的索引,如下图所示。 (2)单击“添加”...
  • bigheadsheep
  • bigheadsheep
  • 2015-02-05 14:08
  • 5463

创建与删除索引

  • yuanzhuohang
  • yuanzhuohang
  • 2010-05-21 19:50
  • 127628

用ALTER TABLE语句来创建索引

7.2.3 用ALTER TABLE语句来创建索引 在已经存在的表上,可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下: 1.          ...
  • baidu_21578557
  • baidu_21578557
  • 2016-07-08 17:47
  • 6654

DB2 创建索引语句

CREATE INDEX GXQUSR.IDX_AGGERATION_PUSH_RECORD_SERIAL_NO ON GXQUSR.FUND_AGGERATION_PUSH_RECORD ( SERIAL_NO ) COLLECT SAMPLED DETAILED S...
  • liuao107329
  • liuao107329
  • 2017-12-05 09:55
  • 543

Oracle创建索引要做到三个适当

在Oracle数据库中,创建索引虽然比较简单。但是要合理的创建索引则比较困难了。笔者认为,在创建索引时要做到三个适当,即在适当的表上、适当的列上创建适当数量的索引。虽然这可以通过一句话来概括优化的索引的基本准则,但是要做到这一点的话,需要数据库管理员做出很大的努力。具体的来说,要做到这个三个适当有如...
  • yang_best
  • yang_best
  • 2014-10-24 10:22
  • 2174

oracle数据库什么情况下创建索引比较好

百度了,,看到一个用户的回答,瞬间清晰了很多,,,,,我记录一下,方便以后斟酌,,, 索引就好象一本字典的目录。凭借字典的目录,我们可以非常迅速的找到我们所需要的条目。数据库也是如此。凭借Oracle数据库的索引,相关语句可以迅速的定位记录的位置,而不必去定位整个表。    虽然...
  • u011410254
  • u011410254
  • 2016-04-04 22:25
  • 5747

oracle数据库建立索引的原则

数据库建立索引的原则 1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。 3,尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时,复合索引也占磁盘空间。 4,...
  • zbyluxzing
  • zbyluxzing
  • 2016-08-08 23:16
  • 2859

Oracle加索引

首先,查看目前已经建立的索引select index_name from all_indexes where table_name = PICTURE;果然比较弱智,只有系统给主键自建的索引:SYS_C55001还犯了一个错误,一般情况下,oracle是不管大小写的,但上面查询语句里面的table_...
  • zhanggnol
  • zhanggnol
  • 2011-06-25 20:02
  • 10984
    个人资料
    • 访问:36698次
    • 积分:357
    • 等级:
    • 排名:千里之外
    • 原创:6篇
    • 转载:3篇
    • 译文:0篇
    • 评论:7条
    文章分类
    最新评论