oracle联合索引匹配

本文主要是介绍oracle联合索引匹配，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

背景： 根据awr检测发现某业务查询耗时较长，1.25s per

报告显示全表查询

sql较简单：

select commonstru0_.PMR_COMMON_STRUCT_ID as PMR_COMMON_STRUCT_1_7_,
       commonstru0_.COMPOUND_ID          as COMPOUND_ID2_7_,
       commonstru0_.CONTROL_CLASS        as CONTROL_CLASS3_7_,
       ...
  from pmr.PMR_COMMON commonstru0_
 where commonstru0_.HOSPITAL_ID = :1
   and commonstru0_.COMPOUND_ID = :2
   and commonstru0_.PATIENT_SN = :3
   and commonstru0_.ELEMENT_CODE = :4

现要对全表扫描sql进行索引优化，进行如下实验：

1. 无索引，filter全表扫描，cost较高

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |   273 |  2515   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |                   |     1 |   273 |  2515   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| PMR_COMMON_STRUCT |     1 |   273 |  2514   (1)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COMMONSTRU0_"."HOSPITAL_ID"='12211422' AND 
              "COMMONSTRU0_"."PATIENT_SN"='300144202008261' AND 
              "COMMONSTRU0_"."COMPOUND_ID"='4652008544' AND 
              "COMMONSTRU0_"."ELEMENT_CODE"='ZDYJ')

2.  全量联合索引，所有子句均添加索引

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                         |     1 |   273 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                       |                         |     1 |   273 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| PMR_COMMON_STRUCT       |     1 |   273 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_PMR_COMMON_STRUCT_1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COMMONSTRU0_"."HOSPITAL_ID"='12211422' AND "COMMONSTRU0_"."PATIENT_SN"='300144202008261' 
              AND "COMMONSTRU0_"."COMPOUND_ID"='4652008544' AND "COMMONSTRU0_"."ELEMENT_CODE"='ZDYJ')

      结论：索引范围查找，但索引占用空间较大

3.  联合索引排除差异较小的字段（hospital_Id），降低索引所占用空间（采用）

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                         |     1 |   273 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                       |                         |     1 |   273 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| PMR_COMMON_STRUCT       |     1 |   273 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_PMR_COMMON_STRUCT_1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("COMMONSTRU0_"."HOSPITAL_ID"='12211422')
   3 - access("COMMONSTRU0_"."PATIENT_SN"='300144202008261' AND 
              "COMMONSTRU0_"."COMPOUND_ID"='4652008544' AND "COMMONSTRU0_"."ELEMENT_CODE"='ZDYJ')

  结论：hospital_id 使用filter， 其他字段仍然可以命中索引

4. 调整where子句顺序，查看对命中联合索引的影响

Plan hash value: 1093457038
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                         |     1 |   273 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY                       |                         |     1 |   273 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| PMR_COMMON_STRUCT       |     1 |   273 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_PMR_COMMON_STRUCT_1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

 结论：调整顺序前后的执行计划相同（Plan hash value: 1093457038 ），说明无需关注where子句的顺序，对命中索引没有影响，优化器自动处理了

这篇关于oracle联合索引匹配的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！