oracle索引实验报告,oracle 索引+案例

在学习索引之前，有必要了解一下B树的创建，B树索引就是为了优化数据库查找。

1.索引的概念

但我们的表中数据很多的时候(比如有1亿条数据)，我们想找出一条符合特定条件的记录就会比较慢，这个时候，我们希望表中的数据是有序的，这样我们可以使用B树，诸如二分法之类的方法加快查询，而不是做全表扫描，但我们每次要查询的数据可能来自不同的列，我们也无法保证插入表中的数据就是有序的，怎么办呢？

1、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度

2、Oracle数据库提供“索引”来解决这个问题，B树索引直接指向包含所查询值的行的位置，减少磁盘I/O。

3、 与所索引的表是相互独立的物理结构(类似，书本 [表] 与目录 [索引] 的关系，无索引，只不过是查询表的速度，性能方面有影响)

4、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引(B树也称为二叉平衡树，具有矮胖，自平衡的特点，每次做DML操作，B树索引都会做一次自平衡，因此索引不适用在DML操作频繁的表中)

5、 语法：CREATE INDEX index ON table (column[, column]...);

我们可以根据我们查询条件要比较的列来创建“索引”，从索引开始查找。

2.索引的分类

2.1 逻辑分类

唯一索引/非唯一索引：对某一列或几列的键值(key)是否是唯一的，当某列任意两行的值都不相同时适合创建唯一索引(CREATE UNIQUE INDEX index ON table (column);)；当表建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立；

对一列或多列建的索引：索引分离于表，作为一个单独的个体存在，除了可以根据单个字段创建索引，也可以根据多列创建索引。Oracle要求创建索引最多不可超过32列。

基于函数(function-based)的索引:基于某些函数索引，当执行某些函数时需要对其进行计算，可以将某些函数的计算结果事先保存并加以索引，提高效率；

组合索引：当两个或多个列经常一起出现在where条件中时，则适合在这些列上同时创建组合索引；

域(domain)索引：索引数据库以外的数据，使用相对较少；

2.2 物理分类

B-tree索引(默认方式)：Root为根节点，branch 为分支节点，leaf 到最下面一层称为叶子节点。每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。叶子节点会存放index entry (索引入口)，每个索引入口对应一条记录(key的值、长度、rowid等)

位图(bitmap)索引：位图索引主要针对大量相同值的列而创建，位图索引通过不同位图取值直接的位运算(与或)，来获取到结果集合向量(计算出的结果)。

3.案例

3.1 创建表

--1)创建表

create table czh_test_index(

id number,

a number,

b number,

c number

);

--2)新增100000条数据

begin

for i in 1 .. 100000 loop

insert into czh_test_index

values

(i,mod(i,2),mod(i,20000),mod(i,20000));

end loop;

commit;

end;

3.2 唯一索引

先修改id为主键(自动生成索引)：

alter table czh_test_index add constraintpk_czh_test_indexprimary key (id) using index;

图1-唯一索引

图2-非主键查询全表扫描

图3

根据图1，2，3可以看出，查询同一条记录，所需要的成本是不一样的。因为图1的查询语句有两个特点，查询条件(id)在索引上。这条语句通过B树索引查询到rowid后，不需要额外在去原来的表里查数据了。为什么呢？因为索引包括根、枝、叶，叶子上存储的是索引值，包括：rowid、键值、键值长度、所属标号。看到没，如果所取的值都在索引上，就可以直接返回了。而图2的查询语句中，查询条件(b)不在索引上，所以只能走全表扫描了。

3.3 组合索引

先给a，b创建组合索引：

create index idx_test_id_ab on czh_test_index(a,b);

图4

图5

图6

根据图4，5，6可以看出，当两个或多个列经常一起出现在where条件中时，则适合在这些列上同时创建组合索引；图4走索引范围扫描，而图5，6走的是全表扫描。

3.4 函数索引

先创建函数索引：CREATE INDEX to_char_dept_name_idx1 ON czh_test_index(to_char(b,'fm99,99.00'));

当查询语句的Where条件中，对于某些列使用了函数表达式时，普通索引对查询没有帮助，如果想利用索引，则必须创建函数索引，比如在下面的例子中

图7

图8

对于图7的查询语句，如果建立普通索引，比如create index idx_test_id_b on czh_test_index(b);

那么上述SQL执行的时候，Oracle是不会走索引的，因此需要建立函数索引，如图8所示。

4.问题

4.1 在什么样的情况下创建索引对加快查询有利呢

答：查询条件中使用到这个列(或者这个列于其他列的组合)，且这个列(或者与其他列的组合)上的数字

范围跨度很大，而大多数情况下我们要获取的数据的量占整个表的数据总量 小于4%；

4.2 在什么样的情况下不适合创建索引呢

答: 1)被查询的表本身就很小，即是是全表扫描也非常快； 或者基于这张表的查询，大多数情况下需要获取

的数据量都超过了总量的4%；或者这张表需要频繁的被更新，建立索引的话会引起索引的频繁更新，从而反而

降低数据库的整体效率。

=========================详细的各类索引后续，等我熟悉执行计划之后再完善=========================