【Oracle11g】15_索引

目录

• 1.索引简介
• 2.索引的分类
• 3.创建标准索引
• 4.分析索引
  • 4.1 案例说明
• 5.唯一索引
• 6.组合索引
• 7.反向键索引
• 8.位图索引
• 9.基于函数的索引
• 10.重建索引
• 11.删除索引
• 12.索引的分区
• 13.获取索引信息

1.索引简介

1）索引是与表相关的一个可选结构
2）用以提高 SQL 语句执行的性能
3）减少磁盘I/O
4）使用 CREATE INDEX 语句创建索引
5）在逻辑上和物理上都独立于表的数据
6）Oracle 自动维护索引

2.索引的分类

索引分为：B树索引（平衡树索引）、位图索引。
B树索引分为：唯一索引、组合索引、反向键索引、基于函数的索引

B树索引的图

首先看根节点块，在根节点块分为三种情况B1、B2、B3,此处以B1块为例子说明，B1表示索引列的值在0~500之间，然后B1块中又拥有分支节点L1、L2、L3，L1表示索引列的值在0~200之间，L2表示索引列的值在200~400之间，L3表示索引列的值大于400，接着在L1下又分为叶子结点，叶子节点L1又分为:R1、R2、R3，其中R1表示索引列的值在0~29直接，R2、R3与R1类似。

3.创建标准索引

CREATE INDEX index_name ON table_name (col_name)
     TABLESPACE index_tbs;

查看索引信息
select * from user_indexes;
查看索引建立在哪个列上
select * from user_ind_columns u where u.index_name='IND1'

4.分析索引

分析语法：

analyze index <index_name> validate structure;

当我们删除数据的时候，某些索引块对应的物理数据假如被删除了，那么索引块可能就存在空间浪费问题，我们称为索引碎片。
查看index_stats表中的del_lf_rows_len/lf_rows_len的值，如果del_lf_rows_len/lf_rows_len的值过低，说明在索引中存在碎片，可以重建索引，来缩小del_lf_rows_len/lf_rows_len的值，减少索引中的碎片。

注意：就是该命令有一个坏处，就是在运行过程中，会锁定整个表，从而阻塞其他session对表进行插入、更新和删除等操作。这是因为该命令的主要目的并不是用来填充index_stats视图的，其主要作用在于校验索引中的每个有效的索引条目都对应到表里的一行，同时表里的每一行数据在索引中都存在一个对应的索引条目。为了完成该目的，所以在运行过程中要锁定整个表，同时对于很大的表来说，运行该命令需要耗费非常多的时间。

4.1 案例说明

-- 建表
create table t(col char(8));

-- 插入数据
begin
	for i in 1..1300000 loop
		insert into t values(ltrim(to_char(i,'00000009')));
		if mod(i, 100)=0 then 
			commit;
		end if;
	end loop;
end;
/

-- 建立索引
create index ind_t on t(col);

-- 查看创建好的索引的pct_used
select name,height,pct_used,del_lf_rows/lf_rows from index_stats where name='IND_T'; -- 执行结果为空,需要先分析

-- 分析索引
analyze index ind_t validate structure;

-- 再次执行查看pct_used
select name,height,pct_used,del_lf_rows/lf_rows from index_stats where name='IND_T';
 
--** 以下是执行结果
NAME                               HEIGHT   PCT_USED DEL_LF_ROWS/LF_ROWS
------------------------------ ---------- ---------- -------------------
IND_T                                   3         90                   0
--**从结果可以看到DEL_LF_ROWS/LF_ROWS为0,说明现在索引没有任何碎片

-- 删除数据
delete t where rownum<1000000;
commit;

-- 再次执行查看pct_used，查看前需要先分析
analyze index ind_t validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats where name='IND_T';
--**
NAME                               HEIGHT   PCT_USED DEL_LF_ROWS/LF_ROWS
------------------------------ ---------- ---------- -------------------
IND_T                                   3         90             0.76923
--**
-- 从结果可以看到现在DEL_LF_ROWS/LF_ROWS（索引碎片比率）的值是0.76923，一般超过0.2，则需要进行索引碎片清理了

为了解决上述问题，可以重建索引，来减小DEL_LF_ROWS/LF_ROWS的值，减少索引中的碎片。

alter index ind_t rebuild;

5.唯一索引

1）唯一索引确保在定义索引的列中没有重复值，空值不受限制，索引列为空的记录可以存在多行
2）Oracle 自动在表的主键列上创建唯一索引
使用CREATE UNIQUE INDEX语句创建唯一索引

CREATE UNIQUE INDEX index_name
     ON table_name (col_name);

6.组合索引

1）组合索引是在表的多个列上创建的索引
2）索引中列的顺序是任意的
3）如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

语法：

CREATE INDEX index_name
     ON table_name(col1_name, col2_name);

7.反向键索引

1）反向键索引反转索引列键值的每个字节
2）通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
创建索引时使用REVERSE关键字

CREATE INDEX index_name 
     ON table_name (col_name) REVERSE;

列数据类似以下这种情况可以使用反向键索引:
1001,1002,1003,1004...
oracle建立反向键索引的时候，实质上就是把索引列反转后再建立索引，反转后如下：
1001,2001,3001,4001...

8.位图索引

1）位图索引适合创建在低基数列上（例如：10000万行记录，但是性别这一列只有2种情况，这就是低基数列）
2）位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
3）节省空间占用
4）如果索引列被经常更新的话，不适合建立位图索引
总体来说，位图索引适合于数据仓库中，不适合OLTP中

语法：

CREATE BITMAP INDEX bit_index
     ON order_master (orderno);

9.基于函数的索引

1）基于一个或多个列上的函数或表达式创建的索引
2）表达式中不能出现聚合函数
3）不能在LOB类型的列上创建
创建时必须具有 QUERY REWRITE 权限

语法：

-- 创建索引
CREATE INDEX lowercase_idx 
     ON toys (LOWER(toyname));
	 
-- 查询

CREATE INDEX lowercase_idx 
     ON toys (LOWER(toyname));

10.重建索引

ALTER INDEX index_name REBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS];

其中：ONLINE使得在重建索引过程中，用户可用对原来的索引进行修改；

• NOLOGGING表示在重建过程中产生最少的重做条目redo Entry；
• COMPUTE STATISTICS表示在重建过程中就生成了oracle优化器所需的统计信息，避免了索引重建之后再进行analyze或dbms_stats来收集统计信息。

11.删除索引

DROP INDEX index_name;

12.索引的分区

可以将索引存储在不同的分区中
与分区有关的索引有三种类型：

• 局部分区索引
  在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致
• 全局分区索引
  在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关
• 全局非分区索引
  在分区表上创建的全局普通索引，索引没有被分区

局部分区索引

create table e1(code number,name varchar2(10))
partition by range(code)
(
	partition p1 values less than(1000),
	partition p2 values less than(2000),
	partition p3 values less than(maxvalue)
);

-- 创建索引
create index ind_e1 on e1(code) local;

全局分区索引

create table e2(code number,name varchar2(10))
partition by range(code)
(
	partition p1 values less than(1000),
	partition p2 values less than(2000),
	partition p3 values less than(maxvalue)
);

-- 创建索引,以下指定的是索引的分区
create index ind_e2 on e2(code) global partition by range(code)
(
	partition p1 values less than(1500),
	partition p2 values less than(maxvalue)
)
;

全局非分区索引

create table e3(code number,name varchar2(10))
partition by range(code)
(
	partition p1 values less than(1000),
	partition p2 values less than(2000),
	partition p3 values less than(maxvalue)
);

-- 创建索引
create index ind_e3 on e3(code) global ;

13.获取索引信息

与索引有关的数据字典视图有：

• USER_INDEXES － 用户创建的索引的信息
• USER_IND_PARTITIONS － 用户创建的分区索引的信息
• USER_IND_COLUMNS － 与索引相关的表列的信息

示例：

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
     FROM USER_IND_COLUMNS
     ORDER BY INDEX_NAME, COLUMN_POSITION;