文档章节

Oracle数据库对象 索引

javayou
 javayou
发布于 2016/05/23 18:43
字数 2339
阅读 3
收藏 0

查询是在表上进行的最频繁的访问。

在查询数据时,很少有用户愿意查询表中的所有数据,除非要对整个表进行处理。
一般情况下用户总是查询表中的一部分数据。

在SELECT语句中,通常需要通过WHERE子句指定查询条件,以获得满足该条件的所有数据。
如果能够在很小的范围内查询需要的数据,而不是在全表范围内查询,那么将减少很多不必要的磁盘1/0 ,查询的速度无疑会大大加快。
提供这种快速查询的方法就是索引。

索引的基本概念

索引是一种建立在表上的数据库对象,它主要用于加快对表的查询操作。

合理使用索引可以大大减少磁盘访问的次数,从而大大提高数据库的性能。
使用索引的主要目的是加快查询速度,另外,索引也可以作为唯一性约束。

如果在表的一个列上建立了唯一性索引,那么系统将自动在这个列上建立唯一性约束,这样可以保证插入这个列的数据是唯一的。

索引究竟是怎样加快查询速度的呢?

原来,索引是建立在表中的某个列或几个列上的,这样的列称为索引列。
在创建索引时,数据库服务器将对索引列的数据进行排序,并将排序的结果存储在索引所占用的存储空间中。
在查询数据时,数据库服务器首先在索引中查询,然后再到表中查询。
因为索引中的数据事先进行了排序,所以只需要很少的查找次数就可以找到需要的数据。

在索引中,不仅存储了索引列上的数据,而且还存储了一个ROWID 的值。
ROWID是表中的一个伪列,是数据库服务器自动添加的,表中的每一行数据都有一个ROWID值,它代表这一行的标识,即一行数据在存储空间的物理位置。
在访问表中的数据时,都要根据这个伪列的值找到数据的实际存储位置,然后再进行访问。
由于索引列上的数据已经进行了排序,在索引中很快就能找到这行数据,然后根据ROWID就能直接到表中找到这行数据了。

需要注意的是,表是独立于索引的,无论对在表上建立了多少索引,无论索引对表中的数据进行什么样的排序,表中的数据都不会有任何变化。

在查询一行数据时,首先在索引中查询该行的行标识,然后根据这个行标识找到表中的数据。
因为索引中的数据是经过排序的,所以采用了折半查找法查找数据,以达到快速查找的目的。

利用折半查找法在索引中查找数据的过程类似于遍历一棵二叉树,首先与根节点比较,如果与查找的数据相同,则一次访问就完成查询。
如果要查找的数据小于根节点,则在根节点的左子树中查找,否则在右子树中查找,这样查找的范围将缩小一半。
按照这种方法,每次将查找范围缩小一半,然后在剩下的节点中继续查找,直到找到所需的数据。

按照索引列的值是否允许重复,索引可以分为唯一性索引和非唯一性索引,其中唯一性索引可以保证索引列的值是唯一的。
按照索引列中列的数目,索引可以分为单列索引和复合索引。
按照索引列的数据的组织方式,索引可以分为B+树索引、位图索引、反向索引和基于函数的索引,这里仅介绍B+树索引的用法。

合理地使用索引固然可以大大提高数据库的查询性能,但是不合理的索引反而会降低数据库的性能,尤其是在进行DML操作时。
在创建索引时,表中的数据将被排序,如果对表进行了DML操作,表中的数据发生了变化,这时索引中的数据也将被重新排序,如果在表上建立了多个索引,那么每个索引中的数据都要被重新进行排序。

这种排序的开销是很大的,尤其是表非常大时。

索引是关系型数据库系统用来提高性能的有效方法之一,索引的使用可以减少磁盘访问的次数,从而大大提高了系统的性能。
但是在设计索引时必须全面考虑在表上所进行的操作,如果在表上进行的主要操作是查询操作,那么可以考虑在表上建立索引,如果在表上要进行频繁
的DML操作,那么索引反而会引起更多的系统开销。

一般来说,创建索引要遵循以下原则:
·如果每次查询仅选择表中的少量行,应该建立索引。

·如果在表上需要进行频繁的DML操作,不要建立索引。
·尽量不要在有很多重复值的列上建立索引。
·不要在太小的表上建立索引。

在一个小表中查询数据时,速度可能已经足够快,如果建立索引,对查询速度不仅没有多大帮助,反而需要一定的系统开销。

索引的创建、修改和删除

索引可以自动创建,也可以手工创建。如果在表的一个列或几个列上建立了主键约束或者
唯一性约束,那么数据库服务器将自动在这些列上建立唯一性索引,这时索引的名字与约束的
名字相同。
手工创建索引需要执行SQL命令,创建索引的命令是CREATE INDEX 。一个用户可以在自
己的模式中创建索引,只要这个用户具有CREATE INDEX这个系统权限。如果希望在其他用户
的模式中创建索引,那么需要具有CREATE ANY INDEX这个系统权限。
CREATE INDEX 命令的语法格式为:

CREATE INDEX 索引名 ON 表名(列1,列2 ...);

在这个索引中,索引列只有一个,这样的索引称为单列索引。

如果要建立复合索引,则要指定多个列。

例如:

CREATE INDEX ind_de_dn ON test(deptno, dname);

复合索引主要用于多个条件的查询语句中。

在默认情况下,创建的索引是非唯一的,也就是说,在表中的索引列上允许存在重复值。
如果要创建唯一性索引,那么需要使用关键字UNIQUE 。

例如:

CREATE UNIQUE INDEX ind_de ON test(deptno);

一般情况下,在指定索引中的列时,要遵循以下原则:

在WHERE子句中经常使用的列上创建索引。
尽量不要在具有大量重复值的列上创建索引。
具有唯一值的列是建立索引的最佳选择,但是究竟是否在这个列上建立索引,还要看是否对这个列经常进行查询。
如果WHERE子句中的条件涉及多个列,可以考虑在这些列上创建一个复合索引。

正如前面所说,合理设计的索引将提高系统的性能,而不合理的索引反而会降低系统性能。
所以,在数据库的运行过程中,要经常利用SQL Trace检查索引是否被使用,检查索引是否像期望的那样提高了数据库的性能。
如果一个索引并设有被频繁地使用,或者一个索引对数据库性能的提高只有微小的帮助甚至设有帮助,这时可以考虑删除这个索引。

索引信息的查询

与索引有关的数据字典有两个: user_indexesuser_ind_columns
例如,要查询索引的类型、所基于的表、是否唯一性索引,以反状态、等信息,可以执行以下查询语句:

SELECT index_type, table_name, status FROM user_indexes WHERE index_name='IND_DE';

下面的查询语句用来获得索引所基于的表和表上的列:

SELECT table_name, column_name FROM user_ind_columns WHERE index_name='IND_DE';

数据字典视图user_ind_columns各列的定义和意义如下

名称         意义

INDEX_NAME    Index name 

TABLE_NAME    Table or cluster name 

COLUMN_NAME   Column name or attribute of object column

COLUMN_POSITION Position of column or attribute within index

COLUMN_LENGTH  Maximum length of the column or attribute, in bytes

CHAR_LENGTH      Maximum length of the column or attribute, in characters

DESCEND      DESC if this column is sorted descending on disk, otherwise ASC

注:cluster 簇表;

attribute 属性

数据字典视图user_indexes常用各列的定义和意义如下:

名称          意义

INDEX_NAME      索引名称 

INDEX_TYPE     索引类型

TABLE_OWNER     对象属主

TABLE_NAME         对象名称     

TABLE_TYPE      对象类型

STATUS       状态

注:数据字典视图user_indexes上列有很多。

本文转载自:http://www.cnblogs.com/jy627625/p/5498179.html

javayou
粉丝 3
博文 52
码字总数 688
作品 0
太原
私信 提问
oracle系列(四)oracle事务和常用数据库对象

博主QQ:819594300 博客地址:http://zpf666.blog.51cto.com/ 有什么疑问的朋友可以联系博主,博主会帮你们解答,谢谢支持! 前言:本次内容的相关知识点我们在学习sqlserver2008R2的时候介绍...

Mr大表哥
2017/05/27
0
0
segment,extent,block

1.segment 段的不同类型: 分区表 tablepartition 表 table 索引 index 簇 cluster 索引分区indexpartition 索引表 index-organized table 临时段 temporary segment 还原段 undosegment 大对......

长平狐
2013/09/17
250
0
ORACLE将执行过的SQL语句存放在内存的共享池

Oracle SQL性能优化深入浅出 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执...

技术小甜
2017/11/09
0
0
《Oracle数据库基础与应用教程》一本Oracle入门教材

《Oracle数据库基础与应用教程》书全面讲述了Oracle数据库的日常管理工作内容。全书共14章,包含Oracle简介和安装、Oracle客户端、管理Oracle环境、Oracle体系结构、管理Oracle存储结构、SQL...

airfish2000
2016/08/30
0
0
Oracle查询数据表结构/字段/类型/大小

Oracle数据库字典 在Oracle的绝大多数数据字典视图中都有象DBATABLES,ALLTABLES和USERTABLES这样的视图家族。Oracle中有超过100个视图家族,下表列出了最重要和最常用的视图家族,需要注意的...

yousss
2018/05/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

归并排序

import java.util.Arrays;/** * Created by chengxiao on 2016/12/8. */public class MergeSort { public static void main(String []args){ int []arr = {9,8,7,6,......

暗中观察
31分钟前
0
0
面试爱奇艺,竟然挂在第5轮……

今天给大家分享我曾经在爱奇艺的面试,过程还是比较有意思的,可以给大家一些参考 <br> 聊骚阶段 嗲妹妹:你好,我是爱奇艺的HR,我们正在招聘运维开发岗位,请问您最近有在看工作机会吗? ...

上海小胖
今天
5
0
Jenkins系列_插件安装及报错处理

进入Jenkins之后我们可以进行插件的安装,插件管理位于以下模块: 发现上面报了一堆错误,是因为插件的依赖没有安装好,那么这一节,就先把这些错误解决掉吧。解决完成后,也就基本会使用插件...

shzwork
今天
3
0
mysql mysql的所有查询语句和聚合函数(整理一下,忘记了可以随时看看)

查询所有字段 select * from 表名; 查询自定字段 select 字段名 from 表名; 查询指定数据 select * from 表名 where 条件; 带关键字IN的查询 select * from 表名 where 条件 [not] in(元素...

edison_kwok
昨天
9
0
利用VisualVM 内存查看

准备工作,建几个测试类。等下就是要查看这几个类里面的属性 package visualvm;public class MultiObject { private String str; private int i; MultiObject(String str...

冷基
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部