深入浅出Oracle索引技术栈
作者：IT邦德
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
（WebjavaPython）工作，主要服务于生产制造
现拥有 Oracle 11g OCP/OCM、
Mysql、Oceanbase（OBCA）认证
分布式TBaseTDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作，在数据库领域有丰富的经验
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
文章目录
前言
索引原理及应用实践
1.导读
对于OLTP系统而言，其操作特点是每次操作，几乎都是访问和处理极少的数据，
比如访问某一张订单，查看某一位客户的资料等。
如果通过在检索条件的相关列上，在选择性较好的列或列的组合上创建索引，
就可以利用索引快迅定位相关记录。
这要比从头至尾扫描整张表的资源开销小得多，效率也自然高得多，执行时长也会短得多。
此外，由于索引只是由一列或少数几列构成，其相比于表中十几，
甚至几十上百列而言，其体积要小得多。
如果所需访问的列（检索条件列和返回的列）均在索引上，则可以避免对相对大的表的访问，而只需要访问体积小得多的索引。这也会带来访问开销的降低，从而提升SQL执行效率的效果。
2.索引分类
2.1 B- tree 索引
索引页块中存储键值和 rowid，常用于 OLTP 系统，
针对基数比较高（high cardinality）的列 （重复值较少)
2.1.1 查看 rowid
alter user scott identified by tiger account unlock; --解锁用户
select rowid,
dbms_rowid.rowid_object(rowid) object#,
dbms_rowid.rowid_relative_fno(rowid) datafile#,
dbms_rowid.rowid_block_number(rowid) block#,
dbms_rowid.rowid_row_number(rowid) row#,
empno,ename from emp;
2.1.2 创建测试表 test1 、索
[email protected]> create table test1 as select * from emp;
[email protected]> create index test_idx1 on test1 (ename);
[email protected]> explain plan for select * from test1 where ename ='SCOTT';
[email protected]> @?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3447293396
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
select * from table(dbms_xplan.display());
[email protected]> drop index test_idx1; --删除索引
2.2 位图索引
一个键值对应很多行（rowid）， 格式：键值 start_rowid end_rowid 位图,索引页块中通过位图的 0 和 1 标识键值和表中行的关系,页块中存储起始 rowid 和结束,rowid,占用空间比较少,针对基数比较低的列（low cardinality）,DML 操作锁定索引 entry,更新代价比较高,适合只读表或 OLAP/DSS 系统 （never updated）
2.2.1 test1 创建位图索引
[email protected]> create bitmap index test_idx1 on test1(job);
[email protected]> explain plan for select * from test1 where job='CLERK';
[email protected]> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2884149098
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST1 | 4 | 348 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | TEST_IDX1 | | | | |
------------------------------------------------------------------------------------------
2.2.2 位图索引锁代价
位图索引一个键值指向多行（成百上千），“牵一发而动全身”，锁代价昂贵，严重影响更新和删除效率
【实验案例1】
session1 更新某 行索引列
[email protected]> select * from test1 order by job;
[email protected]> update test1 set job='CLERK' where empno=7788; --更新 job=ANALYST 的 7788，job 列更新为 CLERK
session2 更新其他行测试
更新 job=ANALYST 的 7902、job=CLERK 的 7900，job 列更新为其他值，锁等待
[email protected]> update test1 set job='test' where empno=7902;
ERROR at line 1:
ORA-01013: user requested cancel of current operation
更新 job=MANAGER 的 7566，job 列更新为非 ANALYST、CLERK 的其他值，正常
[email protected]> update test1 set job='SALESMAN' where empno=7566;
1 row updated.
【实验案例2】
test1 插入数据
begin
for i in 1..999 loop
insert into test1 select * from emp;
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
[email protected]> select count(*) from test1;
COUNT(*)
----------
14000
创建测试表 test2 、b-tree 索引
[email protected]> create table test2 as select * from test1;
[email protected]> create index test2_idx1 on test2(job);
查看索引页块数量
[email protected]> select index_name,index_type,LEAF_BLOCKS from user_indexes;
INDEX_NAME INDEX_TYPE LEAF_BLOCKS
------------------------------ --------------------------- -----------
TEST2_IDX1 NORMAL 37
TEST_IDX1 BITMAP 1
ORDERS_PK NORMAL 0
PK_EMP NORMAL 1
PK_DEPT NORMAL 1
删除测试表
2.3 函数索引
基于表达式或函数包括的列创建索引，它将一个函数计算得到的结果存贮在索引中
2.3.1 创建测试表、普通索引
[email protected]> create table test as select empno,initcap(ename) ename,job from emp;
[email protected]> select * from test;
2.3.2 创建普通索引 查看执行计划
[email protected]> create index ind_test_ename on test(ename);
[email protected]> explain plan for select * from test where ename='Scott';
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 418585065
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ENAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
数据库中存储的数据大小写敏感，应用程序已经将用户输入的数据转换为大写
[email protected]> explain plan for select * from test where upper(ename)='SCOTT';
[email protected]> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 26 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
2.3.3 创建函数索引
[email protected]> create index ind2_test_ename on test(upper(ename));
[email protected]> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IND2_TEST_ENAME FUNCTION-BASED NORMAL
IND_TEST_ENAME NORMAL
[email protected]> explain plan for select * from test where upper(ename)='SCOTT';
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2085671027
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 33 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND2_TEST_ENAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
[email protected]> exec
dbms_stats.gather_table_stats('SCOTT','TEST',cascade=>true,method_opt=>'for columns (upper(ename)) size auto')
[email protected]> drop table test purge;
2.4 反向索引
将正常的键值头尾调换 后再进行存储，RAC 环境中，如果索引列通过序列产生，并发 insert 操作时容易产生索引热块（index hot spots）
buffer busy wait，将字节倒置后组织键值,可以防止叶节点出现热块现象,反向索引不支持索引范围扫描（index range scan）
2.4.1 创建测试表、序列
[email protected]> create table test (id number,name varchar2(20));
[email protected]> create sequence seq1 start with 1 increment by 1;
2.4.2 插入数据
begin
for i in 1..10 loop
insert into test values (seq1.nextval,'OCM');
end loop;
commit;
end;
/
2.4.3 创建反向索引
[email protected]> create index ind_test_id on test(id) reverse;
[email protected]> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IND_TEST_ID NORMAL/REV
2.4.4 查询执行计划
[email protected]> explain plan for select * from test where id=2;
[email protected]> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
[email protected]> explain plan for select * from test where id<2;
[email protected]> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 25 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
2.4.5 重建为普通索引（重新查看）
[email protected]> alter index ind_test_id rebuild noreverse;
[email protected]> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IND_TEST_ID NORMAL
[email protected]> explain plan for select * from test where id<2;
[email protected]> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
[email protected]> drop index IND_TEST_ID;
3.索引访问执行计划
3.1 index unique scan
适合唯一索引的情形
3.2 INDEX RANGE SCAN
大于，小于、或者普通索引等
3.3 INDEX FAST FULL SCAN
3.4 INDEX FULL SCAN
4.索引相关常用命令
5.1 并行度（资源换取时间）
alter table OBJECT_TEST parallel(degree 1) --直接指定表的并行度
alter table OBJECT_TEST noparallel; --取消并行度
SELECT /*+ PARALLEL(8) */ MAX(sal),AVG(comm) FROM emp,dept
select a.TABLE_NAME, a.degree from dba_tables a where a.TABLE_NAME ='OBJECT_TEST'
并行度的优点就是能够最大限度的利用机器的多个cpu资源，是多个cpu同时工作，
从而达到提高数据库工作效率的目的,建议并行度为 2~4 * CPU 数）
5.2 添加主键
alter table EDS_MOD_DEFECT
add constraint EDS_MOD_DEFECT_PK primary key (DATUM, TABLECOMMENT)
using index
tablespace PROPOSAL_DAT_IDX local; --分区索引（非分区，不用加local）
5.3 删除主键
alter table EDS_MOD_DEFECT drop constraint EDS_MOD_DEFECT_PK;
5.4 创建索引
##分区索引（分区增加后，索引表空间按指定的索引表空间分配）
create index EDS_MOD_DEFECT_IDX1 on EDS_MOD_DEFECT(DATUM,TABLECOMMENT) tablespace PROPOSAL_DAT_IDX LOCAL;
##全局索引
create index T_RANGE_IDX1 on T_RANGE(TEST_DATE) tablespace USERS online;
5.5 删除索引
drop index EDS_MOD_DEFECT_IDX1;
5.6 Index Rebuild(一般表空间变)
##分区索引
alter index EDS_MOD_DEFECT_IDX1 rebuild partition EDS_MOD_DEFECT_2003 tablespace PROPOSAL_DAT_IDX online parallel 3;
alter index index_name rebuild partition p_name online parallel 3;
##全局索引
alter index idx_tbl_col rebuild online;
注：在create index ... nologging 和 alter index ... rebuild nologging时，nologging会生效。
在后续DML操作时，会生成redo log,虽然index设置为nologging
5.7.分区增加后，索引表空间默认分区的表空间
create index EDS_GLASS_HIST_IDX_02 on EDS_GLASS_HIST(DATUM) LOCAL
(
PARTITION GLASS_HIST_TP_2001 TABLESPACE EDS_GLS_HIST_IDX_PAR_01,
PARTITION GLASS_HIST_TP_2002 TABLESPACE EDS_GLS_HIST_IDX_PAR_02,
PARTITION GLASS_HIST_TP_2003 TABLESPACE EDS_GLS_HIST_IDX_PAR_03,
PARTITION GLASS_HIST_TP_2004 TABLESPACE EDS_GLS_HIST_IDX_PAR_04,
PARTITION GLASS_HIST_TP_2005 TABLESPACE EDS_GLS_HIST_IDX_PAR_05,
PARTITION GLASS_HIST_TP_2006 TABLESPACE EDS_GLS_HIST_IDX_PAR_06,
PARTITION GLASS_HIST_TP_2007 TABLESPACE EDS_GLS_HIST_IDX_PAR_07,
PARTITION GLASS_HIST_TP_2008 TABLESPACE EDS_GLS_HIST_IDX_PAR_08,
PARTITION GLASS_HIST_TP_2009 TABLESPACE EDS_GLS_HIST_IDX_PAR_09,
PARTITION GLASS_HIST_TP_2010 TABLESPACE EDS_GLS_HIST_IDX_PAR_10,
PARTITION GLASS_HIST_TP_2011 TABLESPACE EDS_GLS_HIST_IDX_PAR_11,
PARTITION GLASS_HIST_TP_2012 TABLESPACE EDS_GLS_HIST_IDX_PAR_12
)
5.8 不可见索引 (Invisible Index) 11g
不可见索引是会被优化器忽略的不可见索引,我们可以将无用或低效的索引设置为不可见索引，当观察一段时间后，发现其对系统性能并无任何影响，那么就可以彻底删除索引了。
create index test_idx1 on test(empno) invisible; --执行计划不识别，即使使用提示hint强制其走这个索引。优化器还是不会走索引扫描
[email protected]> select index_name, status,visibility from user_indexes;
INDEX_NAME STATUS VISIBILIT
------------------------------ -------- ---------
TEST_IDX1 VALID INVISIBLE
ORDERS_PK VALID VISIBLE
PK_EMP VALID VISIBLE
PK_DEPT VALID VISIBLE
select /*+ index(text, idx_test_id) */ * from test where object_id=12;
注：设置参数optimizer_use_invisible_indexes为true后，此时优化器就会走索引范围扫描了，默认为flase
ALTER INDEX TEST_IDX1 VISIBLE; --修改索引可见
oracle如何使得index变成不可用状态，此时Oracle就不维护它了
ALTER INDEX TEST1_IDX1 UNUSABLE;
执行成功后，如果后期需要用到该索引的话，必须重建。重建后自动变成usable状态
ALTER INDEX TEST1_IDX1 REBUILD ONLINE;
5.9 创建唯一索引
create unique index test_idx2 on emp_test(deptno); --不能有重复值（空值除外）,不会创建约束
alter table emp_test add constraint test_idx3 unique(ename); --创建约束，同时创建唯一索引
alter index index_name noparallel ; --设备没有并行
5.10 创建索引unusable
create index test_idx5 on test(sal) unusable;
alter index TEST_IDX5 rebuild; --重建后索引会生效
--针对函数索引，设置enable/disable
alter index ind2_test_ename enable;
alter index ind2_test_ename disable;
select a.INDEX_NAME,a.index_type,a.status,a.FUNCIDX_STATUS from user_indexes a; --函数是否可用于不可用
总结
1.一般不同值分布占全表记录数15%以上就可以建立索引提高效率
select a.table_name,
a.column_name,
a.num_distinct,
round(a.num_distinct*100/b.num_rows) "distinct percent%"
from user_tab_columns a ,user_tables b
where a.table_name=b.table_name and b.table_name='TEST';
2.建索引的原则
索引覆盖避免回表，一般建复合索引；
避免排序；
复合索引等值在前；
3.哪些情况走不上索引呢？
统计信息不准
倾斜值
聚簇因子
隐式转换（表关联的时候，类型转换要注意这种情况）
条件列上存在运算符
索引状态
表很小
null值 要么where条件限制is not null 要么字段属性设为not null
大家点赞、收藏、关注、评论啦 、查看??????微信公众号获取联系方式??????
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
THE END
二维码