【Oracle11g】15_索引

1.索引简介

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

2.索引的分类

索引分为:B树索引(平衡树索引)、位图索引。
B树索引分为:唯一索引、组合索引、反向键索引、基于函数的索引
【Oracle11g】15_索引

B树索引的图
【Oracle11g】15_索引

首先看根节点块,在根节点块分为三种情况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;

【Oracle11g】15_索引

上一篇:SQL Server 数据库清空ldf日志文件


下一篇:MySQL基础(五):用通配符进行过滤