索引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;