oracle 索引

1. 什么是索引

• 索引是数据库对象之⼀，用于加快数据的检索。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量。
• 索引是建⽴在表上的可选对象；索引的关键在于通过⼀组排序后的索引键来取代默认的全表扫描检索发方式，从而提⾼检索效率。
• 索引在逻辑上和物理上都与相关的表和数据⽆关，当创建或者删除⼀个索引时，不会影响基本的表；
• 索引⼀旦建立，在表上进行DML操作时（例如在执⾏插入、修改或者删除相关操作时），oracle会⾃动管理索引，索引删除，不会对表产⽣影响
• 索引对用户是透明的，⽆论表上是否有索引，sql语句的用法不变
• oracle创建主键时会⾃动在该列上创建

2. 索引的原理

1. 若没有索引，搜索某个记录时（例如查找name=‘SCOTT’）需要搜索所有的记录，因为不能保证只有⼀个SCOTT，必须全部搜索⼀遍
2. 若在name上建⽴索引，oracle会对全表进⾏⼀次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询（name为wish）时即可直接查找对应地方
3. 创建了索引并不⼀定就会使用，oracle⾃动统计表的信息后，决定是否使用索引，表中数据很少时使⽤全表扫描速度已经很快，没有必要使用

3. 索引的类型

唯⼀和⾮唯⼀索引	唯⼀索引是基于唯⼀列的索引。当使⽤了唯⼀约束时，由oracle⾃动创建
主索引和次索引	主索引是表的唯⼀索引，它是⾮空且唯⼀的。次索引是表中其他列上的索引，不是唯⼀索引。
组合索引	是包含了表中的两个列或者更多列，还成为拼接索引

4. 索引的种类

• B-tree 索引（默认)
• 位图索引
• 单列索引和复合索引
• 函数索引
• 反键

4.1 B-tree索引

1. oracle中最常⽤的索引；B树索引就是⼀颗⼆叉树；叶⼦节点（双向链表）包含索引列和指向表中每个匹配⾏的ROWID值
2. 所有叶⼦节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同
3. 能够适应精确查询、模糊查询和⽐较查询
   分类：UNIQUE,NON-UNIQUE(默认),REVERSE KEY

例子:创建索引

craete index ind_obj_id on test_object(‘object_id’);

适合使⽤场景：列基数（列不重复值的个数）大时适合使⽤b-tree索引

4.2 单列索引和复合索引

即如果索引建⽴在多个列上，只有它的第⼀个列被where⼦句引⽤时，优化器才会使⽤该索引，即⾄少要包含组合索引的第⼀列

create index ind_no_name on emp(empno, ename);

4.3 位图索引

创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立⼀个位图（位图中，对表中每⼀行使⽤⼀位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换。

create bitmap index index_sno on emp(sno);

4.4 反键索引

• 反键索引除了在索引过程中键值和列数据的字节是相反的以外，在本质上与B-tree是相同的。
• 使⽤反键索引时，数据库不会按照列数据排序
• 索引键值是排序的，其有序性⽆法规避。创建反键索引可以将：
  1000,10001,10011,10111,1100经过反向后的值将是0001,10001,11001, 10011,0011。显然经过位反向处理的有序数据变得⽐较随机了。

create index mgr_idx on emp1(mgr) reverse；

4.5 函数索引

1. 当经常要访问⼀些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
2. 函数索引既可以使⽤B数索引，也可以使⽤位图索引；当函数结果不确定时采⽤B树索引，结果是固定的某几个值时使用位图索引
3. 函数索引中可以使用len、trim、substr、upper（每⾏返回独⽴结果），不能使⽤
   sum、max、min、avg等

create index fbi on emp (upper(name));
select * from emp where upper(name) ='WISH’；

4.6 压缩索引

重复键值只存储⼀次，就是说重复的键值在叶块中就存⼀次，后跟所有与之匹配的 rowid 字符串。

SQL> create index comp_idx on emp1(sal) compress;

4.7 升序或降序

叶节点中的键值排列默认是升序的。

SQL> create index deptno_job_idx on emp1(deptno desc, job asc)；

4.8 查看索引

查看表上具有哪些索引

select index_name,index_type, tablespace_name, uniqueness
  from all_indexes
  where tablename =‘TABLENAME'；

5. 索引里的内容

AAAR+RAAHAAAACTAAH 7788 SCOTT
rowid 18 位，64 进制
包括:所在表的对象号 数据⽂件号 块号 块⾏号 6363
6 位对象号，3 位⽂件号，6 位块号，3 位行号

7. 索引相关视图

user_indexes	查看索引名字、类型、表名、是否唯⼀索引
user_ind_columns	查看索引名、表名、列名

col index_name for a20
col table_name for a10
col column_name for a20
select ic.index_name,ic.table_name, ic.column_name,ix.uniqueness
  from user_indexes ix,user_ind_columns ic
	where ic.index_name = ix.index_name
  	and ic.table_name = 'EMP'

查看执⾏计划

explain plan for select * from emp where
lower(ename)='scott';
@?/rdbms/admin/utlxplp.sql

8. 创建索引

CREATE [UNIQUE]|[BITMAP] INDEX index_name ON
table_name([column1 [ASC|DESC],column2 [ASC|DESC],…] |
[express])
[TABLESPACE tablespace_name]

• [PCTFREE n1]
  指定索引在数据块中空闲空间
• [STORAGE (INITIAL n2)]
• [NOLOGGING] 表示该索引上的记录不进行记录在redo中
• [NOLINE]
• [NOSORT]
  表示创建索引时不进行排序，默认不适⽤，如果数据已经是按照该索引顺序排列的可以使用

8.1 创建索引

• 创建默认索引

CREATE INDEX ind_obj_id ON test_objects(object_id)
  TABLESPACE tbs_ind_emp;

• 创建索引时选择收集优化统计信息

CREATE INDEX ind_obj_id ON	test_objects(object_id)
  TABLESPACE tbs_ind_emp
  COMPUTE STATISTIC；

• 创建唯⼀索引索引

CREATE UNION INDEX ind_obj_id ON test_objects(object_id)
TABLESPACE tbs_ind_emp;

• 创建⾮唯⼀索引索引

CREATE INDEX ind_obj_id ON test_objects(object_id)
TABLESPACE tbs_ind_emp;

• 创建组合索引

CREATE INDEX ind_obj_id ON test_objects(object_id, object_name)
TABLESPACE tbs_ind_emp;

创建唯⼀约束时、创建主键约束时，⾃动创建唯⼀索引

9. 重建/维护索引

索引的数据是跟着基表的DML活动⽽经常发⽣变化的。

基表删除数据后，对应的索引叶节点中空间不会被释放、不会被重用。

在DML操作⼗分频繁的表上的索引，有可能会变得⾮常庞⼤。

9.1 重建索引

• ⽅式⼀：删除原来的索引，重新建⽴索引
• ⽅式⼆：
  ALTER INDEX ind_obj_id REBUILD ; 重建索引，更新时会锁定数据
  ALTER INDEX ind_obj_id REBUILD ONLINE; 在线重建索引，更新不锁定
  ALTER INDEX indobjid REBUILD ONLINE NOLOGGIN； 不记录日志

9.2 修改索引

1. 重命名索引

   alter index inde_sno rename to bitmap_index;
   

2. 合并索引（表使⽤⼀段时间后在索引中会产⽣碎⽚，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引⽅式更好些，⽆需额外存储空间，代价较低）

   alter index inde_sno coalesc;
   

9.3 删除索引

drop index index_sno;

9.4 监控索引的使用

1. 监控索引的有效性。可请求Oracle对索引进⾏监控。

   索引的拥有者执⾏以下命令

   ALTER INDEX ind_o MONITORING USAGE;
   

   终⽌监控活动：

   ALTER INDEX ind_obj_id NOMONITORING USAGE;
   SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = ‘IND_OBJ_ID’;
   INDEX_NAME      TABLE_NAME    	 MON 			USE
   ---------------- ------------------------- ----- ---
   INDO          	TESTOBJECT       YES   			NO
   

9.5 隐藏索引

11g版本后 ,可将索引设置为隐藏模式。主要是对优化器隐藏索引，⽤于调试。

在删除index前，设为隐藏模式

create index ind_xxx on xxx(id) invisible;
alter index ind_xxx invisible;
alter index ind_xxx visible;

查看索引是否为隐藏模式

select index_name , status , visibility from dba_indexes;

不对优化器隐藏索引

alter session set optimizer_use_invisible_indexes=true；

10. 创建索引的准则

• 需要访问的数据不超过表中额4%-5%。使⽤索引教唆数据需要进⾏两次数据读取的操作，即读索引和读表。
• 相对较小的表可以不是⽤索引。
• 为所有表创建主键，在指定⼀列作为主键时，Oracle⾃动创建索引。
• 对包括使⽤多表连接操作中使⽤的列，建⽴索引
• 在where⼦句中频繁使⽤的列上创建
• 在包括order by 、group by操作的列，以及涉及排序操作的 union、distinct操作中涉及到的列上创建索引。因为索引是已经被排序的
• 被频繁更改的列由于涉及开销问题，理论上不创建索引
• 只对⾼选择性的表建⽴索引即 对⼏乎没有相同值得表建⽴

注意事项

1. 通配符在搜索词⾸出现时，oracle不能使⽤索引
   例如：
   我们在name上创建索引

   create index index_name on emp('empno');
   

   下⾯的⽅式oracle不适⽤name索引

   select * from emp where name like ‘%wish%’;
   

   如果通配符出现在字符串的其他位置时，优化器能够利⽤索引；如下：

   select * from emp where name like 'wish%';
   

2. 不要在索引列上使⽤not，可以采⽤其他⽅式代替如下

   select * from emp where not (score=100);
   select * from emp where score <> 100;
   

   替换为

   select * from emp where score>100 or score <100
   

3. 索引上使⽤空值⽐较将停止使用索引

   select * from emp where score is not null;