Oracle：6、索引

目录

O、前言

一、建立索引

1.1B树索引（Balanced）

1.2位图索引

1.3反向键索引

1.4函数索引（B树索引的一种特例）

二、修改索引

2.1合并索引和重建索引

2.2删除索引

2.3查看索引信息

三、索引组织表

---

O、前言

索引是一种与表相关的可选数据结构，用于加速数据的存取。索引与表一样需要存储空间，它存放在索引段中。

1. 建立索引时，oracle先对建立索引的字段进行排序（一般是升序），然后按照排序后的顺序将该索引字段与对应的ROWID保存到索引中（ROWIID是物理地址，ROWID的介绍参考数据库表结构。这样就类似于目录，索引字段是标题，ROWID是页码。）
2. 索引与键（约束），索引是存储在数据库中的一种实体结构，键只是一个逻辑上的概念，oracle中键是与完整性约束相对应的。
3. 当定义主键约束或唯一性约束的时候，oracle会自动在相应的约束列上建立唯一索引。
4. 索引的数量与DML操作是互斥的（存在索引时，表增加或者删除数据，同时也会去修改对应索引中的记录）
5. 当经常使用某2个字段做and查询时，可以建立复合索引

create index index_name on table_name(column1,column2);

 

一、建立索引

1.1B树索引（Balanced）

create index index_name on table_name(column) [pctfree size] [tablespace tablespace_name];

1、B树索引是oracle中默认的、最常用的索引类型，默认该索引升序排列。
2、B树索引由根块、分支块、叶块3部分组成。其中数据集中在叶块中。

• 根块：索引顶级块，包含指向下一级节点的信息。
• 分支块：包含指向下一级节点（分支块或叶块）的信息。
• 叶块：包含索引入口数据，索引入口包含索引列的值和记录行对应的物理地址ROWID（索引入口数据唯一，但对应的记录不一定唯一）

3、这样建立索引后的查询将减少磁盘的IO操作，通过层级目录定位到指定的数据。否则会需要进行全盘扫描。
 

1.2位图索引

create bitmap index index_name on table_name(column) [tablespace tabspace_name];

alter system set create_bitmap_area_size = size scope=spfile; --修改建立位图索引时，分配的位图区大小，默认8MB

--修改建立位图索引时，分配的位图区大小，默认8MB。数值越大，建立位图索引的速度越快。修改该参数需要重启数据库。  size单位为字节（B）。

alter system set bitmap_merge_area_size = size  scope=spfiile; --修改合并位图时分配的内存大小

--修改合并位图时分配的内存大小，默认1MB，size单位为字节（B）。修改后需要重启数据库。这参数越大，执行and、or、not时，位图合并的速度也越快。

 

1. 位图索引是通过给定的索引列值，快速的找到对应的记录。
2. 主要考虑B树索引对于“基数低”的列会返回大量记录而考虑使用的（如：性别列，只有男女，那么进行B树索引，则会返回一半的数据，那么它索引的意义并不大。）
3. 位图索引将根据索引列的所有值，建立对应不同的位图（如：性别列，建立2个位图，位图男和位图女。）当行数据匹配位图值时，相应位置存储1，否则存储0。
4. 位图索引在执行and、or、not时候，效率显著，它可以将多个位图进行“或”操作等。
5. 执行count(column)列时，可以直接访问索引获得统计数据。

图1.2 位图索引示意图

 

1.3反向键索引

create index index_name on table_name(column) reverse [tablespace tablespace_name];
alter index index_name rebuild reverse;  --修改索引，建立反向键索引。

1. 对于单调递增的列，由于数据是单向堆放的，会导致B树索引单侧臃肿。而反向的概念是，将值进行反转：1972反转为2791。这样反转后，递增的列就是非递增的了，可以均匀的分布在B树索引的各个叶子节点中。
2. 常用于序列产生的主键中。

1.4函数索引（B树索引的一种特例）

create  index index_name on table_name(function(column));

1、当查询常基于某个函数时，可以建立函数索引。那么它不再使用列值作为索引入口，而是将函数的返回值作为索引入口，进行B树划分。
2、常用于名字查询的大小写转换。

select * from table_name where  lower(name_column) = 'abcd';

3、建立函数索引需要拥有query rewrite系统权限，如果要再其他模式中建立则需要create any index 和global query rewrite权限。

 

二、修改索引

2.1合并索引和重建索引

alter index index_name coalesce deallocate unused;   --对B树索引进行合并操作。

 -- 对B树索引进行合并操作。

alter index index_name rebuild [tablespace tablespace_name];   --重建B树索引

-- 重建B树索引（在建立好之后会自动删除原来的索引），【可选项：指定重建后索引的存放位置】。

2.2删除索引

drop index index_name; 

--对于通过create index 语句显式建立的所有，可以通过该语句删除。而定义约束时，oracle自动创建的索引，需要删除或者禁用该约束本身。

2.3查看索引信息

select index_name,index_type,uniqueness 
from {dba_indexes|all_indexes|user_indexes} 
where owner='' and table_name='';

• dba_indexes:所有索引。
• all_indexes:当前用户可访问的所有索引
• user_indexes:当前用户的索引信息
• uniqueness:索引的唯一性

select column_name,column_position,column_length 
from {dba_ind_columns|all_ind_columns|user_ind_columns} 
where  index_name='';

• column_name:列名
• column_position:索引的位置
• column_length:索引列长度
• dba_ind_columns:所有索引的表列信息
• all_ind_columns:当前用户可访问的索引信息
• user_ind_columns:当前用户的索引信息

select tablespace_name,segment_type,bytes 
from {dab_segments|user_segments} 
where segment_name = '';

查询索引段位置及其大小

select column_expression from {dba_ind_expression|user_ind_expressions} where index_name='';

查询函数索引的信息

三、索引组织表

对于普通的标准表，他的存储方式是采用堆组织方式的，其存储结构是将记录无序地存放在数据段中。

而索引组织表就是结合了B树索引，将表通过主键进行排序然后按照B树规则进行组织，存放到数据段中。它是将整个数据存放进去，而不像索引仅仅存放物理地址。

注：B树索引是通过索引字段，在索引中查找到对应ROWID，即物理地址，通过ROWID再从普通表中查找对应的数据。B树索引去除了这个对应过程，将索引字段直接指向了数据，所以这里要求，索引组织表必须具有主键约束（即要求他是唯一的，非空的）

create table table_name(column_name1 number primary key,column_name2...) 
organization index tablespace tablespace_name;

索引组织表维护比较困难，进行插入等操作效率低。所以它适用于数据字典等查询频繁而修改稀少的表。
 

alter table table_name move tablespace tableespace_name; --移动索引组织表。

--移动索引组织表。

create table table_name_new 
as select * from table_name; 
--talbe_name为索引组织表，这种方式可以将索引组织表转换为普通表。

--talbe_name为索引组织表，这种方式可以将索引组织表转换为普通表。