oracle sql优化案例1（使用组合索引） .

原创

mb6446438c6e361 2023-05-08 14:34:14 博主文章分类：oracle数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者mb6446438c6e361的原创作品，请联系作者获取转载授权，否则将追究法律责任

Bad sql

SELECT T3.CONFLICT_ID,
       T3.LAST_UPD,
       T3.CREATED,
       T3.LAST_UPD_BY,
       T3.CREATED_BY,
       T3.MODIFICATION_NUM,
       T3.ROW_ID,
       T3.ATTR_04,
       T3.ATTR_03,
       T2.VAL,
       T3.ATTR_01,
       T3.ATTR_02,
       T1.NAME,
       T2.VAL1
  FROM user.S_ORG_EXT T1, user.CX_LOOKUP T2, user.CX_POS_LOG_INFOT3
 WHERE T3.ATTR_02 = T1.PAR_ROW_ID
   AND T3.ATTR_01 = T2.NAME1
   AND T2.TYPE = 'PROVIDER'
   AND (T3.TYPE = 'INFO')
   AND (T3.ATTR_04 = :1 AND T3.ATTR_02 = :2)

原因分析：对CX_POS_LOG_INFO表全表扫描，其实统计信息也不准确，表的大小应该500M左右了

--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     | Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 1762745057 ----|       |     |   3655 |
|NESTED LOOPS                    |                     |     1 |   1K|   3655 |
| NESTED LOOPS                   |                     |     1 | 109 |      2 |
|  TABLE ACCESS BY INDEX ROWID   |S_ORG_EXT            |     1 |  25 |      1 |
|   INDEX UNIQUE SCAN            |S_ORG_EXT_U3         |    1 |      |      2 |
|  TABLE ACCESS BY INDEX ROWID   |CX_LOOKUP            |     1 |  84 |      1 |
|   INDEX RANGE SCAN             |CX_LOOKUP_U1         |    1 |      |      2 |
| TABLE ACCESS FULL              |CX_POS_LOG_INFO      |    1 |    1K|   3653 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      71207 consistent gets
      71187 physical reads
          0 redo size
       1131 bytes sent via SQL*Net to client
        460 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

解决思路：

手工添加组合索引，使查询走组合索引，查询从65秒下降到1秒，逻辑读从71207（556M）下降到21

create indexSIEBEL.IDX_CX_POS_LOG_INFO_U1 on SIEBEL.CX_POS_LOG_INFO (ATTR_02, ATTR_04)

tablespace SIEB_IDX

Execution Plan

----------------------------------------------------------

0     SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=1278)
   1   0   TABLE ACCESS (BY INDEX ROWID)OF 'CX_POS_LOG_INFO' (Cost=1 Card=1 Bytes=1169)
   2   1     NESTED LOOPS (Cost=3 Card=1Bytes=1278)
   3   2       NESTED LOOPS (Cost=2Card=1 Bytes=109)
   4   3         TABLE ACCESS (BY INDEXROWID) OF 'S_ORG_EXT' (Cost=1  Card=1Bytes=25)
   5   4           INDEX (UNIQUE SCAN) OF'S_ORG_EXT_U3' (UNIQUE) (Cost=2 Card=1)
   6   3         TABLE ACCESS (BY INDEXROWID) OF 'CX_LOOKUP' (Cost=1  Card=1Bytes=84)
   7   6           INDEX (RANGE SCAN) OF'CX_LOOKUP_U1' (UNIQUE) (Cost=2 Card=1)
   8   2       INDEX (RANGE SCAN) OF'IDX_CX_POS_LOG_INFO_U1'  (NON-UNIQUE)(Cost=1 Card=93)
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         18 consistent gets
          0 physical reads
          0 redo size
       1448 bytes sent via SQL*Net to client
        655 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed