ORACLE 索引

最新推荐文章于 2024-09-15 16:36:43 发布

x1n1010

最新推荐文章于 2024-09-15 16:36:43 发布

阅读量942

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/x1n1010/article/details/12882527

版权

Oracle 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

今天整理ORACLE索引，ORACLE索引有很多类型，包括有：

B树索引(默认类型)

位图索引
HASH索引
索引组织表索引
反转键(reverse key)索引
基于函数的索引
分区索引(本地和全局索引)

位图连接索引

今天主要针对oracle最常见、应用最广泛B树索引(默认类型)、位图索引进行整理，直接进入正文：

首先先了解创建索引的语法：

UNIUQE:创建唯一索引，创建索引的列的值必须不相同的，是唯一
BITMAP：创建位图索引，不指定就是创建B-TREE（oracle默认）索引
<schema>.<index_name>：指定索引名称
<schema>.<table_name>：指定索引所应用的表名
<column_name>：为哪个列创建索引
TABLESPACE:指定表所在的表空间
STORAGE：可进一步设置表空间参数
LOGGING | NOLOGGING：索引是否重做日志
COMPUTE STATISTICS：创建新索引时收集统计信息
NOCOMPRESS | COMPRESS：是否使用索引键压缩（键压缩可以删除键中出现的重复值，减少存储空间使用同时效率会降低）
NOSORT：表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
PARTITION | GLOBAL PARTITION：可以对分区表或未分区表上创建的索引进行分区

例1：为test2表创建默认B-tree索引

CREATE INDEX t_name_index on test2 (name) --其他项默认

--创建B-TREE索引成功！就这么简单，那么什么是B-tree所以呢？往下看

什么是B-tree索引？
B-tree索引时最常见的一种类型，也是oracle默认类型。B树索引指的是一种平衡树，使用平衡算法来管理索引的。
B树索引从结构上看是一个倒立的树状结构，它有两种类型的数据块：分支块和叶块。分支块的作用是根据条件查询定位其他分支块和叶块的位置。
叶块是用于存储索引的列值和对应数据行的物理地址，该物理地址用rowid来表示，rowid是一个指针，用它来指向数据行的物理地址，这是oracle数据库
访问数据行的最快方法。索引中只保存索引的列值和rowid，才是它的规模比表要小的多，所以对索引查询的速度要远高于对表的检索速度。
B-tree索引检索原理
举例说明:查询ID为2199的记录，那么执行 SELECT * FROM TEST WHERE ID=2199;
如果没有索引那么要查询呢2199的记录就需要对全表扫描，现在对ID列上建立B-tree索引，使用索引查询的原理步骤为：
1、读取最上层的分支块数据，得知id为2199记录的索引在第二层分支块B3锁定位的范围内。
2、读取第二层分支块B3中数据，得知ID为2199记录的索引列在第二层分支块B3下面的叶块B31中存储
3、读取叶块中B31数据，获取id为2199的记录的rowid，然后得到rowid得到该记录。
可见索引检索数据至读取了3个数据块，要远远低于全表读取数据的数量。大大降低了I/0操作数据，从而提高了查询效率。

如果查询是不等查询，则从第三层开始不必重新沿着向最上层分支块查找，直接沿着双向链表进行查询即可，如下图代码所示：
SELECT * FROM TEST WHERE ID BETWEEN '2199' AND '2999';
此时查询该记录的步骤为：
1、读取最上层的分支块数据，得知id为2199的记录的索引列值在第二层分支块B3所在的范围内。
2、读取第二层B3中的数据，得知ID为2199的记录索引列值在第二层分支块下面叶块B31中存储。
3、读取叶块B31中数据，获得ID为2199的rowid, 然后根据rowid得到该记录。
4、沿着叶块双向链表结构向后依次获取2999的记录rowid,然后根据rowid得到相应记录。

对于索引来说，B-tree索引结构思一个十分有效的数据结构，其层次通常很短，一般不会超过三层，在一个千万数据级的表中查询一条记录，一般也只需读取
3个数据块。

当一条新数据插入表中，新的信息同时也被插入到叶块中；当一个叶块被写满后，被插入新的行信息叶块就会被分割为两个叶块，新的叶块定位信息就会被放入
上层分支块中；如果分支块也被写满，那么它也会自动分割为两个分支块，新的分支块地位信息同时放入到上层分支块中，以此类推直到全部信息写入各个级别
的分支块。在此过程中平衡算法会自动调整各个分支块所能定位的下级分支块和叶块的数量以及叶块中保存索引列值数量，以使各分支尽量达到平衡。

虽然B-tree索引应用最为广泛，但也不适用于所有情况，适用B树索引的场合有：
1、表中存储的数据库很多
2、列中存储的不同数据值很多
3、查询的数据量不超过全表的5%，否则应使用全表扫描

用于查看索引信息user_indexes：
select index_name,blevel+1,table_name from user_indexes; --blevel表示该索引有几个分支块，+1表示包含叶块数量

例子2：为test2表创建位图索引

CREATE BITMAP INDEX t_name_index on test2 (name) --test2表创建位图索引
虽然B-tree索引应用很广，但在一个列有多很多重复值时，B-TREE索引的叶块就会存储很多重复的数据，反而会影响数据查询效率。对于一个列中重复值很多时，
采用位图索引进行检索查询效率会更高。ORACLE创建位图索引时，会为索引创建一个位图，该位图由二进制数组成。0表示该列无值，1表示该列有值。

直接上例子：SELECT s_id,s_name,s_political,s_language from student;

sid                   s_name                   s_political                   s_language
0807020225   张三             共青团员           日语
0807020226   李四                           共青团员           英语
0807020227   王五                           群众                  英语
0807020228   赵六                           共青团员                   英语
0807020229   孙琦                           共青团员           日语
0807020230   王八                           预备党员           英语
0807070130   王八1                       预备党员           英语
0807070201   王八2                党员               英语

如以s_political、s_language为条件查询，那么位图索引二进制表示为：
共青团员：11011000
群众：    00100000
预备党员：00000110
党员:     00000001

日语：10001000
英语：01110111

--以上是位图索引所表现的搜索方式，0表示该列无值，1表示该列有值。
假设要查询政治面貌是‘共青团员’语种是‘英语’的学生学号、姓名，可执行下面的语句：
SELECT S_ID,S_NAME,FROM STUDENT WHERE s_political='共青团员' and s_language='英语'
共青团员：11011000
英语：    01110111
共青团员和英语匹配为1的。那么记录为2,4
对于表列中重复数据很多时使用位图索引可以查询提高效率。当对表总进行增删改时会重新组织索引项，每个位图索引项都包含了表中大量数据行的rowid,因此对于频繁使用增删改操作
表不适用于使用位图索引。使用位图所有的场合有：
1、表中有大量数据存在
2、表中列有重复数值不少
3、列用于布尔运行（or,and,not）;