深入浅出Oracle索引技术栈 -阿里云开发者社区

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 、索
SCOTT@PROD> create table test1 as select * from emp;
SCOTT@PROD> create index test_idx1 on test1 (ename);
SCOTT@PROD> explain plan for select * from test1 where ename ='SCOTT';
SCOTT@PROD> @?/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());
SCOTT@PROD> 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 创建位图索引
SCOTT@PROD> create bitmap index test_idx1 on test1(job);
SCOTT@PROD> explain plan for select * from test1 where job='CLERK';
SCOTT@PROD> 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 更新某 行索引列
SCOTT@PROD> select * from test1 order by job;
SCOTT@PROD> update test1 set job='CLERK' where empno=7788;  --更新 job=ANALYST 的 7788，job 列更新为 CLERK
session2 更新其他行测试
更新 job=ANALYST 的 7902、job=CLERK 的 7900，job 列更新为其他值，锁等待
SCOTT@PROD> 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 的其他值，正常
SCOTT@PROD> 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.
SCOTT@PROD> select count(*) from test1;
  COUNT(*)
----------
     14000
创建测试表 test2 、b-tree 索引
SCOTT@PROD> create table test2 as select * from test1;
SCOTT@PROD> create index test2_idx1 on test2(job);
查看索引页块数量
SCOTT@PROD> 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 创建测试表、普通索引
SCOTT@PROD> create table test as select empno,initcap(ename) ename,job from emp;
SCOTT@PROD> select * from test;
2.3.2 创建普通索引 查看执行计划
SCOTT@PROD> create index ind_test_ename on test(ename);
SCOTT@PROD> 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 |
----------------------------------------------------------------------------------------------
数据库中存储的数据大小写敏感，应用程序已经将用户输入的数据转换为大写
SCOTT@PROD> explain plan for select * from test where upper(ename)='SCOTT';
SCOTT@PROD> 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 创建函数索引
SCOTT@PROD> create index ind2_test_ename on test(upper(ename));
SCOTT@PROD> 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
SCOTT@PROD> 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 |
-----------------------------------------------------------------------------------------------
SCOTT@PROD> exec 
dbms_stats.gather_table_stats('SCOTT','TEST',cascade=>true,method_opt=>'for columns (upper(ename)) size auto')
SCOTT@PROD> drop table test purge;

2.4 反向索引

   将正常的键值头尾调换 后再进行存储，RAC 环境中，如果索引列通过序列产生，并发 insert 操作时容易产生索引热块（index hot spots）
buffer busy wait，将字节倒置后组织键值,可以防止叶节点出现热块现象,反向索引不支持索引范围扫描（index range scan）
2.4.1 创建测试表、序列
SCOTT@PROD> create table test (id number,name varchar2(20));
SCOTT@PROD> 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 创建反向索引
SCOTT@PROD> create index ind_test_id on test(id) reverse;
SCOTT@PROD> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IND_TEST_ID                    NORMAL/REV
2.4.4 查询执行计划
SCOTT@PROD> explain plan for select * from test where id=2;
SCOTT@PROD> 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 |
-------------------------------------------------------------------------------------------
SCOTT@PROD> explain plan for select * from test where id<2;
SCOTT@PROD> 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 重建为普通索引（重新查看）
SCOTT@PROD> alter index ind_test_id rebuild noreverse;
SCOTT@PROD> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IND_TEST_ID                    NORMAL
SCOTT@PROD> explain plan for select * from test where id<2;
SCOTT@PROD> 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 |
-------------------------------------------------------------------------------------------
SCOTT@PROD> 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强制其走这个索引。优化器还是不会走索引扫描
SCOTT@PROD> 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

深入浅出Oracle索引技术栈

1.导读

2.索引分类

2.1 B- tree 索引

2.2 位图索引

2.3 函数索引

2.4 反向索引

3.索引访问执行计划

3.1 index unique scan

3.2 INDEX RANGE SCAN

3.3 INDEX FAST FULL SCAN

3.4 INDEX FULL SCAN

4.索引相关常用命令

总结

热门文章

最新文章

相关课程

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

深入浅出Oracle索引技术栈

1.导读

2.索引分类

2.1 B- tree 索引

2.2 位图索引

2.3 函数索引

2.4 反向索引

3.索引访问执行计划

3.1 index unique scan

3.2 INDEX RANGE SCAN

3.3 INDEX FAST FULL SCAN

3.4 INDEX FULL SCAN

4.索引相关常用命令

总结

热门文章

最新文章

相关课程

相关电子书

推荐镜像