一个超简单的SQL如何优化

zmyzmy726

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 编辑 ]

wangzk0206

但从这个SQL来讲你的USER_MERCHANT_INFO表完全没有用处，
请给出你的索引的定义顺序。
如果可以的话：索引顺序建议：TXN_TYPE,TXN_STA_CD,TRANS_FINISH_TIME

Maclean Liu(刘相兵

给出你想要用的 索引的名字

并针对以上SQL 语句 运行一个sql health check

http://www.askmaclean.com/archiv ... h-check-script.html

pkmonster

建议用这个索引:
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;