sql优化,该如何处理

sql优化

SQL code

--查询某日期区间,未退款且已支付成功的支付记录SELECT service_order,sum(PAYMENT_AMOUNT) FROM PAYMENT A1 WHERE EXISTS(    SELECT order_id  FROM ctoc_order A2 WHERE 1=1    and A2.order_id=A1.service_order    AND (Order_Status =1 OR Order_Status=2)    AND trunc(ORDER_CREATE_TIME) between to_date('2012-05-01','yyyy-MM-dd') and to_date('2012-06-01','yyyy-MM-dd'))and payment_status=0group by service_orderhaving sum(PAYMENT_AMOUNT)>0订单表:ctoc_orderid:order_id状态:Order_Status支付表:PAYMENT订单表外键;service_order金额:PAYMENT_AMOUNT--求加速,求优化```

SQL code

SELECT service_order,sum(PAYMENT_AMOUNT) FROM PAYMENT A1 WHERE EXISTS(    SELECT order_id  FROM ctoc_order A2 WHERE 1=1    and A2.order_id=A1.service_order    AND (Order_Status =1 OR Order_Status=2)    AND trunc(ORDER_CREATE_TIME) between to_date('2012-05-01','yyyy-MM-dd') and to_date('2012-06-01','yyyy-MM-dd'))and payment_status=0group by service_orderhaving sum(PAYMENT_AMOUNT)>0既然你过滤了sum(PAYMENT_AMOUNT)>0是不是开始panment_amount>0的条件就可以写在where那里呢，而不是group by后判断
[解决办法]
1.使用hint，改变or的执行计划。
2.你的date应该可以直接比较吧，不使用trunc，例如数据库中日期 ’2012-1-2 01：01：20‘ 判断它是否在1月付款、退款，不用做时间处理的。
SQL code
SELECT /*+USE_CONCAT*/ service_order,sum(PAYMENT_AMOUNT) FROM PAYMENT A1 WHERE EXISTS(    SELECT order_id  FROM ctoc_order A2 WHERE 1=1    and A2.order_id=A1.service_order    AND (Order_Status =1 OR Order_Status=2)    AND trunc(ORDER_CREATE_TIME) between to_date('2012-05-01','yyyy-MM-dd') and to_date('2012-06-01','yyyy-MM-dd'))and payment_status=0group by service_orderhaving sum(PAYMENT_AMOUNT)>0