【Oracle11g】15_索引

2022-10-15 13:24:43

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表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。

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,pct_used from index_stats where name=‘IND_T‘; -- 执行结果为空,需要先分析

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

-- 再次执行查看pct_used
select name,pct_used from index_stats where name=‘IND_T‘;
 
--** 以下是执行结果
NAME                             PCT_USED
------------------------------ ----------
IND_T                                  90
--**从结果可以看到当前的pct_used是90%，使用率达到90%，最大的pct_used就是90%，说明现在索引没有任何碎片

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

-- 再次执行查看pct_used，查看前需要先分析
analyze index ind_t validate structure;
select name,pct_used from index_stats where name=‘IND_T‘;
--**
NAME                             PCT_USED
------------------------------ ----------
IND_T                                  45
--**

为了解决上述问题，可以重建索引，来提高pct_used的值，减少索引中的碎片。

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;