oracle联合索引匹配

最新推荐文章于 2024-04-03 06:24:17 发布

zetor_major

最新推荐文章于 2024-04-03 06:24:17 发布

阅读量1.7k

点赞数

分类专栏：数据库文章标签： oracle 数据库索引 sql

本文链接：https://blog.csdn.net/z2926781/article/details/120744148

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

针对全表扫描导致的SQL查询性能问题，进行了索引优化实验。实验包括：1)无索引的全表扫描，成本较高；2)创建全量联合索引，查询效率提升但占用空间大；3)建立联合索引排除部分字段，降低成本并节省空间；4)调整WHERE子句顺序，对执行计划无影响。最终选择联合索引排除差异小字段的方案，兼顾性能和存储空间。

摘要由CSDN通过智能技术生成

背景： 根据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子句的顺序，对命中索引没有影响，优化器自动处理了