高频执行sql 6wgswwwgrm66a 索引优化
sql: 6wgswwwgrm66a
SELECT A0.blob$entrySetadHocAcl ,
A0.classnameA2A2 ,
A0.branchIdA2typeDefinitionRefe,
A0.idA3A2ownership ,
A0.idA2A2 ,
A0.typeadministrativeLock ,
A0.classnamekeydomainRef ,
A0.securityLabels ,
A0.entrySetadHocAcl ,
A0.statestate ,
A0.idA3domainRef ,
A0.classnamekeyA2ownership ,
A0.idA2A2 docId
FROM WTDocument A0
WHERE
(
(
A0.latestiterationInfo = :1
)
AND
(
A0.idA2typeDefinitionReference = :2
)
AND
(
A0.statecheckoutInfo IN (:3 ,:4 ,:5 ,:6 ,:7 )
)
AND
(
A0.updateStampA2 BETWEEN TO_DATE(:8 ,:9 ) AND TO_DATE(:10 ,:11 )
)
)
AND
(
A0.markForDeleteA2 = 0
)
ORDER BY A0.createStampA2 DESC
1、单表查询,查看执行计划,索引 WTDOCUMENT$COMPOSITE5 返回2523行记录,而最终只返回2行记录,索引选择性不好
Plan hash value: 2715925389
----------------------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | 6 | SELECT STATEMENT | | | | 47 (100)| |
| 1 | 5 | SORT ORDER BY | | 2 | 2212 | 47 (3)| 00:00:01 |
|* 2 | 4 | FILTER | | | | | |
| 3 | 3 | INLIST ITERATOR | | | | | |
|* 4 | 2 | TABLE ACCESS BY INDEX ROWID| WTDOCUMENT | 2 | 2212 | 46 (0)| 00:00:01 |
|* 5 | 1 | INDEX RANGE SCAN | WTDOCUMENT$COMPOSITE5 | 2523 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
2、查看where条件相关列的数据分布,重建索引
SQL> drop index WCADMIN.WTDOCUMENT$COMPOSITE5;
SQL> create index WCADMIN.WTDOCUMENT$COMPOSITE5 on WCADMIN.WTDOCUMENT (STATECHECKOUTINFO, IDA3A2OWNERSHIP,UPDATESTAMPA2,IDA2TYPEDEFINITIONREFERENCE) tablespace INDX;
3、确认执行计划更新,通过索引只返回3行记录,索引选择性比较好
---------------------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | 6 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | 5 | SORT ORDER BY | | 1 | 1114 | 2 (50)| 00:00:01 |
|* 2 | 4 | FILTER | | | | | |
| 3 | 3 | INLIST ITERATOR | | | | | |
|* 4 | 2 | TABLE ACCESS BY INDEX ROWID| WTDOCUMENT | 1 | 1114 | 1 (0)| 00:00:01 |
|* 5 | 1 | INDEX RANGE SCAN | WTDOCUMENT$COMPOSITE5 | 3 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------