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

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

          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

转自：http://blog.csdn.net/hijk139/article/details/7301139