索引Index
索引Index
1 索引Index
索引：
- 是一个方案对象
- 通过指针加速Oracle服务器的查询速度
- 通过使用快速路径访问方法来快速定位数据，可以减少磁盘I/O索引与表相互独立
- Oracle服务器自动使用和维护索引
索引与优化调优非常密切
2 创建索引
在一个或者多个字段上创建索引：
提高对employees表中last_name列的查询访问速度：
create index emp_ename_idx on emp(ename);
3 B-tree索引的结构
3.1 索引里的内容
列的值 (键值) + rowid
select ename,rowid from emp order by ename;
3.2 rowid
rowid是伪列，oracle专用的虚拟列，每个表的每一行都有rowid
每一行的rowid是全局唯一的（行的身份证号）
AAAR9VAAHAAAACXAAH SCOTT
rowid18位，64进制，包括：所在表的对象号、数据文件号、块号、块行号
6363：6位对象号，3位文件号，6位块号，3位行号
4 创建索引注意事项
为了优化连接操作，可以在外键列上创建索引，加快匹配行的查找
索引能够提高select语句的效率，但执行DML语句时，oracle要维护索引，会做很多的递归操作，有维护成本
5 索引相关的数据字典视图
user_indexes 查看索引名字、类型、表名、是否唯一索引
user_ind_columns 查看索引名、表名、列名
col index_name for a20
col table_name for a20
select index_name,index_type,table_name from user_indexes;
col column_name for a20
select index_name,table_name,column_name from 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';
6 常见B树索引创建语法
create table emp1 as select * from emp;
1）唯一索引，指键值不重复
SCOTT@orcl> create unique index empno_idx on emp1(empno);
2）非唯一索引
SCOTT@orcl> create index empno_idx on emp1(empno);
3）组合索引（Composite）：基于两个或多个列的索引
SCOTT@orcl> create index job_deptno_idx on emp1(job,deptno);
4）反向键索引（Reverse）：将字节倒置后组织键值。当使用序列产生主键索引时，可以防止叶节点出现热块现象（考点），缺点是无法提供索引范围扫描
SCOTT@orcl> create index mgr_idx on emp1(mgr) reverse;
5）函数索引（Function base）：以索引列值的函数值为键值去组织索引
SCOTT@orcl> create index fun_idx on emp1(lower(ename));
6）压缩（Compress）：重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串
SCOTT@orcl> create index comp_idx on emp1(sal) compress;
7）升序或降序（Ascending or descending）：叶节点中的键值排列默认是升序的
SCOTT@orcl> create index deptno_job_idx on emp1(deptno desc, job asc);
7 基于函数的索引
查看执行计划
explain plan for select * from emp where ename=upper('scott');
@?/rdbms/admin/utlxplp.sql
explain plan for select * from emp where lower(ename)='scott';
@?/rdbms/admin/utlxplp.sql
创建基于函数的索引
create index lower_ename_idx on emp(lower(ename));
再次查看执行计划
explain plan for select * from emp where lower(ename)='scott';
@?/rdbms/admin/utlxplp.sql
8 重建索引
语法：alter index index_name rebuild online;
批量重建索引：
select 'alter index ' || index_name || ' rebuild online;' scripts from user_indexes;
9 删除索引
一个无用的索引会降低DML效率，还会占用存储空间
10 索引不可用unusable和不可见invisible
10.1 索引不可用unusable
仅仅保存索引定义，不删除索引，也不更新索引
alter index ind_test_id unusable;
索引被设定为unusable后，如再次使用需要做rebuild
alter index ind_test_id rebuild;
10.2 索引不可见invisible
在11g里，Oracle提供了一个新特性（Index Invisible）来降低直接删除索引或禁用索引的风险。可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible（visible）
索引不可见其实是对优化器来说不可见，索引维护还是正常进行的
alter index ind_test_id invisible;
select index_name,status,visibility from user_indexes where table_name='TEST';
alter index ind_test_id visible;
select index_name,status,visibility from user_indexes where table_name='TEST';
11 扩展补充：监控索引的使用
开启监控索引的使用情况：
alter index table_name.index_name monitoring usage;
关闭监控索引的使用情况：
alter index table_name.index_name nomonitoring usage;
例子eg：
开启索引监控
alter index pk_dept monitoring usage;
执行查询
select * from dept where deptno=10;
查看索引是否被使用
select * from v$object_usage;
关闭监控
alter index pk_dept nomonitoring usage;
