各位帮忙看个sql执行效率的问题吧
SELECT ROWNUMBER ()
OVER (ORDER BY JS_SERVICE_REPORT_PART.REPORT_NO DESC)
AS ROWNUMBER_,
JS_SERVICE_REPORT_PART.REPORT_NO,
JS_SERVICE_REPORT_PART.PRTREQUEST_NO,
...--一堆字段
FROM JS_SERVICE_REPORT_PART
LEFT OUTER JOIN JS_SERVICE_REPORT JS_SERVICE_REPORT
ON (JS_SERVICE_REPORT_PART.REPORT_NO =
JS_SERVICE_REPORT.REPORT_NO)
LEFT OUTER JOIN JS_COMMON_PART JS_COMMON_PART
ON (JS_SERVICE_REPORT_PART.PART_NO = JS_COMMON_PART.PART_NO)
LEFT OUTER JOIN JS_COMMON_SERVICE_DEALER JS_COMMON_SERVICE_DEALER
ON (JS_SERVICE_REPORT.DEALER_NO =
JS_COMMON_SERVICE_DEALER.DEALER_NO)
LEFT OUTER JOIN JS_COMMON_MODEL JS_COMMON_MODEL
ON (JS_SERVICE_REPORT.MODEL_NO = JS_COMMON_MODEL.MODEL_NO)
LEFT OUTER JOIN JS_SERVICE_FEE_CLOSING_WRSPART JS_SERVICE_FEE_CLOSING_WRSPART
ON (JS_SERVICE_REPORT_PART.REPORT_NO =
JS_SERVICE_FEE_CLOSING_WRSPART.WRSREQUEST_NO)
AND (JS_SERVICE_REPORT_PART.PART_NO =
JS_SERVICE_FEE_CLOSING_WRSPART.WORSE_PART_NO)
AND (JS_SERVICE_REPORT_PART.SERIAL_NUMBER =
JS_SERVICE_FEE_CLOSING_WRSPART.WRSSERIAL_NUMBER)
WHERE (JS_SERVICE_REPORT.STATUS = '20')
AND JS_SERVICE_REPORT.AUDITING_DATE + 3 MONTHS <
'2013-08-28 07:29:07'
AND JS_SERVICE_FEE_CLOSING_WRSPART.WRSREQUEST_NO IS NULL
AND JS_SERVICE_REPORT_PART.OUTDATE_RETURN = 0
AND JS_SERVICE_REPORT_PART.RETURN_QUANTITY > 0
AND EXISTS
(SELECT 1
FROM JS_WRSPART_RETURN_DETAIL A
JOIN
JS_WRSPART_RETURN_REQUEST B
ON A.REQUEST_NO = B.REQUEST_NO
WHERE B.STATUS = '30'
AND A.SERVICE_REPORT_NO =
JS_SERVICE_REPORT_PART.REPORT_NO
AND A.WORSE_PART_NO =
JS_SERVICE_REPORT_PART.PART_NO
AND A.SERVICE_SERIAL_NUMBER =
JS_SERVICE_REPORT_PART.SERIAL_NUMBER)
ORDER BY JS_SERVICE_REPORT_PART.REPORT_NO DESC
关键就是这个EXISTS导致效率很低，
外表和内表数据量差不多大，都是2W多条，大家看看能怎么优化？
我分不多，还希望各位大牛能够不吝赐教啊
[解决办法]
建议从索引入手解决。
采用exists的话，要确保exists子查询的效率比较高。也可以尝试把exists转成关联语句。
光是看语句很难调的，要知道表数据量，关联和过滤字段的过滤性，表上的索引等信息。
可以尝试用db2自带的db2advis进行索引建议
