24 积分	0 好友	2 主题

发消息

一个超简单的SQL如何优化

1^#

发表于 2012-7-13 19:47:51 | 查看: 4185| 回复: 3

SELECT count(*)
FROM CIECCPAY.PAY_DTL P
LEFT JOIN CIECCPAY.USER_MERCHANT_INFO U ON P.MER_ID = U.MER_ID
WHERE P.TXN_STA_CD in('2','6')
AND P.TXN_TYPE = '00'
AND P.TRANS_FINISH_TIME >=
   TO_DATE('2011-03-05 0:00:00', 'yyyy/mm/dd HH24:MI:SS')
AND P.TRANS_FINISH_TIME <=
   TO_DATE('2012-03-15 23:59:59', 'yyyy/mm/dd HH24:MI:SS')

SQL如上，执行计划如下

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |             |    1 | 21 |  8135 (1)| 00:01:38 |
| 1 |  SORT AGGREGATE             |             |    1 | 21 |          |       |
|*  2 | TABLE ACCESS BY INDEX ROWID| PAY_DTL    |  4125 | 86625 |  8135 (1)| 00:01:38 |
|*  3 | INDEX SKIP SCAN          | IDX_PAY_DTL2 | 12726 |    |  3332 (1)| 00:00:40 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P"."TRANS_FINISH_TIME"<=TO_DATE(' 2012-03-15 23:59:59', 'syyyy-mm-dd
            hh24:mi:ss') AND "P"."TRANS_FINISH_TIME">=TO_DATE(' 2011-03-05 00:00:00',
            'syyyy-mm-dd hh24:mi:ss'))
3 - access("P"."TXN_TYPE"='00')
   filter("P"."TXN_TYPE"='00' AND ("P"."TXN_STA_CD"='2' OR "P"."TXN_STA_CD"='6'))

日期字段没有索引，加上索引后，单日期条件的where很快，但是执行SQL无论如何也不用我的日期索引，加了ORDER,RULE,INDEX等hint均不走，目前觉得用日期索引扫描出来，再做其他2个条件筛选速度会更快，可不知为何CBO不选，望指教如何优化。
附注：pay_dtl为大表，400多W数据
      USER_MERCHANT_INFO 为小表，800数据

[ 本帖最后由 zmyzmy726 于 2012-7-13 19:50 编辑 ]

分享0

收藏0 回复只看该作者道具举报

pkmonster

4^#

发表于 2012-7-13 22:05:42

建议用这个索引:
CREATE INDEX PAY_DTLL01 ON PAY_DTL(TXN_TYPE, TRANS_FINISH_TIME);

SQL改写为:
SELECT COUNT(*)
FROM  (
SELECT MER_ID
FROM CIECCPAY.PAY_DTL P
WHERE P.TXN_STA_CD in('2','6')
AND P.TXN_TYPE = '00'
AND P.TRANS_FINISH_TIME >=
      TO_DATE('2011-03-05 0:00:00', 'yyyy/mm/dd HH24:MI:SS')
AND P.TRANS_FINISH_TIME <=
      TO_DATE('2012-03-15 23:59:59', 'yyyy/mm/dd HH24:MI:SS')) TMPT
LEFT JOIN  CIECCPAY.USER_MERCHANT_INFO U ON P.MER_ID = U.MER_ID;

回复只看该作者道具举报