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

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

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

1recursive calls

0db block gets

71207consistent gets

71187physical reads

0redo size

1131bytes sent via SQL*Net to client

460bytes received via SQL*Net from client

1SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

0rows 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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=1278)

10 TABLE ACCESS (BY INDEX ROWID)OF 'CX_POS_LOG_INFO' (Cost=1 Card=1 Bytes=1169)

21 NESTED LOOPS (Cost=3 Card=1Bytes=1278)

32 NESTED LOOPS (Cost=2Card=1 Bytes=109)

43 TABLE ACCESS (BY INDEXROWID) OF 'S_ORG_EXT' (Cost=1 Card=1Bytes=25)

54 INDEX (UNIQUE SCAN) OF'S_ORG_EXT_U3' (UNIQUE) (Cost=2 Card=1)

63 TABLE ACCESS (BY INDEXROWID) OF 'CX_LOOKUP' (Cost=1 Card=1Bytes=84)

76 INDEX (RANGE SCAN) OF'CX_LOOKUP_U1' (UNIQUE) (Cost=2 Card=1)

82 INDEX (RANGE SCAN) OF'IDX_CX_POS_LOG_INFO_U1' (NON-UNIQUE)(Cost=1 Card=93)

Statistics

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

0recursive calls

0db block gets

18consistent gets

0physical reads

0redo size

1448bytes sent via SQL*Net to client

655bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed