oracle 索引
在关系数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。 对于数据库来说,索引是一个必选项,但对于现在的各种大型数据库来说,索引可以大大提高数据库的性能,以至于它变成了数据库不可缺少的一部分。
索引分类: 逻辑分类 singlecolumnorconcatenated 对一列或多列建所引 uniqueornonunique 唯一的和非唯一的所引,也就是对某一列或几列的键值(key)是否是唯一的。 Function-based 基于某些函数索引,当执行某些函数时需要对其进行计算,可以将某些函数的计算结果事先保存并加以索引,提高效率。 Doman 索引数据库以外的数据,使用相对较少 物理分类 B-Tree:normalorreversekeyB-Tree索引也是我们传统上常见所理解的索引,它又可以分为正常所引和倒序索引。 Bitmap:位图所引,后面会细讲
B-Tree 索引 B-Treeindex也是我们传统上常见所理解的索引。B-tree(balancetree)即平衡树,左右两个分支相对平衡。 B-Treeindex Root为根节点,branch为分支节点,leaf到最下面一层称为叶子节点。每个节点表示一层,当查找某一数据时先读根节点,再读支节点,最后找到叶子节点。叶子节点会存放indexentry(索引入口),每个索引入口对应一条记录。 Indexentry的组成部分: Indexentryentryheader 存放一些控制信息。 Keycolumnlength 某一key的长度 Keycolumnvalue 某一个key的值 ROWID 指针,具体指向于某一个数据 创建索引: 用户登录: SQL> conn as1/as1 Connected. 创建表: SQL> create table dex (id int,sex char(1),name 10)); Table created. 向表中插入1000条数据 SQLbegin 2 for i in 1..1000 3 loop 4 insert into dex values(i,'M',0); line-height:1.5!important">chongshi'); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. 查看表记录 SQLselect * from dex; ID SE NAME ---------- -- -------------------- 索引分离于表,作为一个单独的个体存在,除了可以根据单个字段创建索引,也可以根据多列创建索引。Oracle要求创建索引最多不可超过32列。 SQLindex dex_index2 on dex(sex,name); Index created. SQL> OBJECT_NAME OBJECT_TYPE ------------------------------------------------------------------------------ DEX TABLE DEX_IDX1 INDEX DEX_INDEX2 这里需要理解:如上面查找的列子,通过索引的方式先找到第23条数据,再找到第32条数据,这样就能快速的锁定一个查找的范围,如果每条数据都要从根节点开始查找的话,那么效率就会非常低下。 位图索引 位图索引主要针对大量相同值的列而创建。拿全国居民登录一第表来说,假设有四个字段:姓名、性别、年龄、和身份证号,年龄和性别两个字段会产生许多相同的值,性别只有男女两种值,年龄,1到120(假设最大年龄120岁)个值。那么不管一张表有几亿条记录,但根据性别字段来区分的话,只有两种取值(男、女)。那么位图索引就是根据字段的这个特性所建立的一种索引。 BitmapIndex 从上图,我们可以看出,一个叶子节点(用不同颜色标识)代表一个key,startrowid和endrowid规定这种类型的检索范围,一个叶子节点标记一个唯一的bitmap值。因为一个数值类型对应一个节点,当时行查询时,位图索引通过不同位图取值直接的位运算(与或),来获取到结果集合向量(计算出的结果)。
举例讲解: 假设存在数据表T,有两个数据列A和B,取值如下,我们看到A和B列中存在相同的数据。 对两个数据列A、B分别建立位图索引:idx_t_bita和idx_t_bitb。两个索引对应的存储逻辑结构如下: Idx_t_bita索引结构,对应的是叶子节点: Idx_t_bitb索引结构,对应的是叶子节点:
对查询“select*fromtwhereb=1and(a=’L’ora=’M’)” 分析:位图索引使用方面,和B*索引有很大的不同。B*索引的使用,通常是从根节点开始,经过不断的分支节点比较到最近的符合条件叶子节点。通过叶子节点上的不断Scan操作,“扫描”出结果集合rowid。 而位图索引的工作方式截然不同。通过不同位图取值直接的位运算(与或),来获取到结果集合向量(计算出的结果)。 针对实例SQL,可以拆分成如下的操作: 1、a=’L’ora=’M’ a=L:向量:1010 a=M:向量:0001 or操作的结果,就是两个向量的或操作:结果为1011。 2、结合b=1的向量 中间结果向量:1011 B=1:向量:1001 and操作的结果,1001。翻译过来就是第一和第四行是查询结果。 3、获取到结果rowid 目前知道了起始rowid和终止rowid,以及第一行和第四行为操作结果。可以通过试算的方法获取到结果集合rowid。 位图索引的特点: 1.Bitmap索引的存储空间节省 2.Bitmap索引创建的速度快 3.Bitmap索引允许键值为空 4.Bitmap索引对表记录的高效访问 创建位图索引: 查看表记录 SQLfrom dex; ................... ID SEX NAME -------- -- -------------------- 993 G chongshi 994 G chongshi 995 G chongshi 997 G chongshi 998 G chongshi 999 G chongshi 1000 rows selected. 对于上面表来说sex(性别)只有两种值,最适合用来创建位图所引 创建索引: SQLcreate bitmap index my_bit_idx on dex(sex); Index created. 查看创建的所引 SQL------------------------------------------------------------------------------ MY_BIT_IDX 通过上面的命令就可以重现建立一个索引,oracle重建立索引的过程: 1、锁表,锁表之后其他人就不能对表做任何操作。 2、创建新的(干净的)临时索引。 3、把老的索引删除掉 4、把新的索引重新命名为老索引的名字 5、对表进行解锁。 移动所引: 其实,我们移动索引到其它表空间也同样使用上面的命令,在指定表空间时指定不同的表空间。新的索引创建在别位置,把老的干掉,就相当于移动了。 index orders_region_id_idx rebuild tablespace index03;
在线重新创建索引: 上面介绍,在创建索引的时候,表是被锁定,不能被使用。对于一个大表,重新创建索引所需要的时间较长,为了满足用户对表操作的需求,就产生的这种在线重新创建索引。 index orders_id_idx rebuild online;创建过程: 1、锁住表 2、创建立临时的和空的索引和IOT表用来存在on-goingDML。普通表存放的键值,IOT所引表直接存放的表中数据;on-gongDML也就是用户所做的一些增删改的操作。 3、对表进行解锁 4、从老的索引创建一个新的索引。 5、IOT表里存放的是on-goingDML信息,IOT表的内容与新创建的索引合并。 6、锁住表 7、再次将IOT表的内容更新到新索引中,把老的索引干掉。 8、把新的索引重新命名为老索引的名字 9、对表进行解锁 整合索引碎片: 如上图,在很多索引中有剩余的空间,可以通过一个命令把剩余空间整合到一起。 index orders_id_idx coalesce;删除索引: drop index hr.departments_name_idx;
分析索引
检查所引的有效果,前面介绍,索引用的时间久了会产生大量的碎片、垃圾信息与浪费的剩余空间了。可以通过重新创建索引来提高所引的性能。 可以通过一条命令来完成分析索引,分析的结果会存放在在index_stats表中。 查看存放分析数据的表: SQLcount(*) from index_stats; COUNT(*) -------- 0 执行分析索引命令: SQL> analyze index my_bit_idx validate structure; Index analyzed. 再次查看 index_stats 已经有了一条数据 SQL1 把数据查询出来: SQLselect height,name,lf_rows,lf_blks,del_lf_rows from index_stats; HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS -------- ---------------------------------------------------------------------- ---------- ----------- 2 MY_BIT_IDX 1000 3 100分析数据分析: (HEIGHT)这个所引高度是2,(NAME)索引名为MY_BIT_IDX,(LF_ROWS)所引表有1000行数据,(LF_BLKS)占用3个块,(DEL_LF_ROWS)删除100条记录。 这里也验证了前面所说的一个问题,删除的100条数据只是标记为删除,因为总的数据条数依然为1000条,占用3个块,那么每个块大于333条记录,只有删除的数据大于333条记录,这时一个块被清空,总的数据条数才会减少。 转载地址:http://www.cnblogs.com/fnng/archive/2012/10/10/2719221.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |