如何优化下面SQL语句

我有一个sql语句，查询时间比较长

SELECT * FROM 
(SELECT TEMP.*,ROWNUM RN FROM(SELECT ENP.ENP_NAME ,DETAIL.B_ENP_NAME ,GOOD.DRUG_NAME ,GOOD.PASS_NUM ,GOOD.MANU_NAME ,SALE.LOT_NUM ,NVL(DETAIL.SALE_AMOUNT,'0') || NVL(DETAIL.UOM,'') ,TO_CHAR(SALE.CREATE_DATE,'YYYY-MM-DD')   
FROM SALEDETAIL DETAIL 
LEFT JOIN SALE SALE ON SALE.ID=DETAIL.MAIN_ID 
LEFT JOIN GOODS_INFO GOOD ON GOOD.ID=SALE.DRUG_ID 
LEFT JOIN ENTERPRISE ENP ON ENP.ID=SALE.S_ENP_ID 
WHERE SALE.IS_SUBMIT=1   AND ENP.REGION_ID LIKE '31%'   
ORDER BY TO_CHAR(SALE.CREATE_DATE,'YYYY-MM-DD') DESC ) 
TEMP WHERE ROWNUM <=10) WHERE RN > 0

其中SALEDETAIL 大概有26万条记录
SALE 有20万条记录
GOODS_INFO 有1-2万条记录
ENTERPRISE大概有1-2条记录

目前这个查询需要耗时十几秒，太慢了，有什么好优化的地方吗(去掉order by到时很快，但客户是要求安时间排序)。SALEDETAIL 和SALE每天都在万数量级增加，数据库采用的ORACLE.

这个分页语句查询在去掉order by之后还是速度很快的。

但在做统计总记录数是慢了。
SELECT COUNT(*) FROM (SELECT DETAIL.ID FROM IDS_SALEDETAIL DETAIL LEFT JOIN SALE SALE ON SALE.ID=DETAIL.MAIN_ID LEFT JOIN GOODS_INFO GOOD ON GOOD.ID=SALE.DRUG_ID LEFT JOIN ENTERPRISE ENP ON ENP.ID=SALE.S_ENP_ID ) TOTALTABLE
统计的速度和获取分页的速度不是一个数量等级的差别。

评论

2 楼 wuming_player 2008-02-21  

(1)表的数据量不小，要确保表上有索引，查询SQL的优化，索引起着非常重要的作用。
(2)left join 改为 (+).
(3)加上ORALCE的hint。
(4)order 的字段要加上索引。或者SQL语句中不要order，查询结果中在order也行。

1 楼 shxiao 2008-01-11  

不要使用like, 在SALE.CREATE_DATE上建立函数索引