排行榜sql语句优化

[yuanhuoqing]

现象

对dba提供过来的耗资源的sql进行分析发现类似下面sql重复率比较高

select *
from (
select f.name as variance_name, a.*,(a.last_count_order-a.count_order) as rise_diff ,
b.name as t_name,b.parent_id as t_parent_id,b.forum_dir,b.forum_id as t_forum_id,
b.pub_url as t_pub_url,b.pub_dir ,d.price as area_price , 
nvl(e.min_price,0) as min_price, nvl(e.max_price,0) as max_price
from PDL_PRODUCT a, PDL_PRODUCT_TYPE b , (
SELECT * 
FROM PP_PRICE
WHERE PRICE BETWEEN 1000 AND 4500 AND AREA_ID = 100) d,eml_price e ,pdl_product_variance f
where a.status!=0 and a.review_status<>4 and a.order_type<2 and a.concept is null 
and a.type_id=b.id and b.status=1 and b.type=3 and b.parent_id=20937 and a.type_id= 47071 
and a.id=d.product_id and e.product_id(+)= a.id and a.variance_id=f.id(+) order by a.last_count_order asc)
where rownum <= 10

方案

这个sql都是查全国指导价价格区间的排行榜，根据崇绮的建议，全国指导价没必要联pp_price表查询了，直接用PDL_PRODUCT的price可以了，并对价格等没有参数化条件参数化，优化的sql如下：

select *
from (
select f.name as variance_name, a.*,(a.last_count_order-a.count_order) as rise_diff ,
b.name as t_name,b.parent_id as t_parent_id,b.forum_dir,b.forum_id as t_forum_id,
b.pub_url as t_pub_url,b.pub_dir ,a.price as area_price , 
nvl(e.min_price,0) as min_price, nvl(e.max_price,0) as max_price
from PDL_PRODUCT a, PDL_PRODUCT_TYPE b ,eml_price e ,pdl_product_variance f
where a.status!=0 and a.review_status<>4 and a.order_type<2 and a.concept is null 
and a.price  BETWEEN 1000 AND 45000
and a.type_id=b.id and b.status=1 and b.type=3 and b.parent_id=20937 and a.type_id= 47071 
and e.product_id(+)= a.id and a.variance_id=f.id(+) order by a.last_count_order asc)
where rownum <= 10

根据dba对这两条sql的分析，优化后的sql可以提高14%左右的性能，

因此修改对应的程序，把查全国报价价格区间的排行榜的程序作相应的调整。

结果

在修改程序过程中发现现有程序都是把按价格区间、品牌专区、属性来排行等不同业务的sql拼装混在一起，无法抽取出来。只能在原来恶心的sql上继续拼装。拼装sql是无法避免的，建议不要把很多业务的sql拼装混在一起，也就是每个方法负责单一的业务职责，这样方便后续的优化和代码的可维护性比较强。