首页 > 数据库 > Oracle > 正文
Oracle之SQL优化-索引的基本原理(二)
2017-07-15 10:37:08       个评论    来源:旺旺的博客  
收藏    我要投稿

Oracle之SQL优化-索引的基本原理(二)。

1、为什么使用索引?

(1)、原因

索引中只有一列,io小,所以较快;

索引中此列是排序的,二叉查找,提高查询速度。

(2)、原因分析

索引是对数据库表中一列或多列的值进行排序的一种结构。

索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时,若要对表进行查询有2中搜索方式:

第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,

这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;

第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)

快速找到表中对应的记录。

注:当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。

2、什么情况下适合建立索引:

(1)表的主键、外键必须有索引。

(2)经常与其它表进行连接的表,在连接字段上应该建立索引。

(3)经常出现在WHERE子句中的字段,特别是大表的字段,应该建立索引。

(4)索引应该建在选择性高的字段上。

(5)索引应该建在小字段上,对于大的文本字段甚至超长字段,不适合建索引。

(6)复合索引的建立需要进行仔细分析。

(7)正确选择复合索引中的主列字段,一般是选择性较好的字段。

(8)如果单字段查询很少甚至没有,那么可以建立复合索引;否则考虑单字段索引。

(9)如果复合索引中包含的字段经常单独出现在WHERE子句中,那么分解为多个单字段索引。

(10)如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段。

(11)如果既有单字段索引,又有这几个字段上的复合索引,那么一般可以删除复合索引。

(12)频繁进行DML操作(insert、update、delete)的表,不要建立太多的索引。

(13)删除无用的索引,避免对执行计划造成负面影响。

3、索引创建的策略

(1)导入数据后再创建索引。

(2)不需要为很小的表创建索引。

(3)对于取值范围很小的字段(比如性别字段)应当建立位图索引。

(4)限制表中的索引的数目。

(5)为索引设置合适的PCTFREE值。

(6)存储索引的表空间最好单独设定。

4、如何对索引进行操作:

(1)、索引存储位置——数据库文件:

数据库文件中存储着用户数据(表、索引等)、数据字典、存储过程、函数和数据包的代码、

用来排序的临时数据以及回滚段数据等。

(2)、查看索引:

ORACLE数据字典视图的种类分别为:USER,ALL 和 DBA。

  USER_*:有关用户所拥有的对象信息,即用户自己创建的对象信息;

  ALL_*:有关用户可以访问的对象的信息,即用户自己创建的对象的信息加上

   其他用户创建的对象但该用户有权访问的信息;

  DBA_*:有关整个数据库中对象的信息

注:如果数据库中的索引数据很多,尽量避免查询ALL_INDEXES、DBA_INDEXES。

系统视图存放的是索引名称,对应的表和列等:

all_ind_columns/dba_ind_columns/user_ind_columns

  select * from user_indexes where table='表名' ;
  select * from user_ind_columns where index_name=upper('&index_name');

(3)清理索引碎片

a.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起,并不会改变索引的物理组织结构)

    alter index emp_pk coalesce;

b.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置,并且可以将索引移动到

其它的表空间中,重建索引实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引)

    alter index emp_pk rebuild;

(4)删除索引

   drop index emp_ename; 

如果索引中包含损坏的数据块,或者包含过多的存储碎片,需要首先删除这个索引,然后再重建它。

如果索引是在创建约束时由oracle自动产生的,可以通过禁用约束或删除约束的方法来删除对应的索引。

在删除一个表时,oracle会自动删除所有与该表相关的索引。

5、常用到的一些索引操作

(1)查询一张表里面索引

 select * from user_indexes where table_name=upper('tableName'); 

(2)查询被索引字段

 select * from user_ind_columns where index_name=('indexName');

(3)给某一字段创建索引

 create index index_name on table_name(col_name);

(4)查看用户下的索引

  select  * from user_indexes-          -----查看当前用户下的所有索引
  select  * from user_indexes where table_name='A';      -----查看当前用户下表A的索引
  drop index index_name去掉索引 
  select index_name,index_type,status,blevel from user_indexes where table_name = '?';  
           -----查看某一个表的所有索引
  select table_name, index_name, column_name, column_position from user_ind_columns 
  where  table_name='?';    ----查看索引的构成

(5)建索引

 Create unique clustered index 索引名on 表名(字段1)  --单索引
 Create index 索引名 on 表名(字段1,字段2)  -------复合索引

点击复制链接 与好友分享!回本站首页
上一篇:oracle用户权限设置
下一篇:Oracle之SQL优化-索引的基本原理(一)
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站