sql优化，生产真实案例4

高频执行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 |
----------------------------------------------------------------------------------------------------------------