简单改写SQL达到优化目的

* from (select t.row_id, t.supplier_name, t.tel, address, t.contact, t.contact_post, t.contact_mobi, t.contact_tel, case when project_supp_type = 0 then to_number(nvl(t.discount_rate, 0)) when project_supp_type2 = 0 then to_number(nvl(t.discount_rate2, 0)) when project_supp_type3 = 0 then to_number(nvl(t.discount_rate3, 0)) when project_supp_type4 = 0 then to_number(nvl(t.discount_rate4, 0)) when project_supp_type5 = 0 then to_number(nvl(t.discount_rate5, 0)) when project_supp_type6 = 0 then to_number(nvl(t.discount_rate6, 0)) end as rate from md_supplier t where t.supplier_type = 4 and (project_supp_type = 0 or project_supp_type2 = 0 or project_supp_type3 = 0 or project_supp_type4 = 0 or project_supp_type5 = 0 or project_supp_type6 = 0) and t.status = 1 and exists (select * from md_project mp, md_project_supplier mps where mp.row_id = mps.project_id and mp.status = 1 and supplier_id = t.row_id) order by rate desc, t.supplier_name) where rownum <= 5 View Code

 

 这是原始sql，sql运行4秒出结果，网友反映比较慢

看了执行计划

执行计划没有问题，驱动表全表扫描也只是返回27条，无伤大雅，被驱动表view VW_SQ_1

被驱动表内走了 merge jion，merge jion比nl要强一下，进行全表扫描之后再排序，之后再连接

问题要看exists中的表 返回227行，在view中 exists中的表有相当于一个驱动表了，他要驱动id 10，每次查询都要全表 ，还要227次，就慢了，就需要改写

将它提取出来，不让他扫描多次，这就是思路

改写之后的sql

with ttt as (select *
                    from md_project mp, md_project_supplier mps
                   where mp.row_id = mps.project_id
                     and mp.status = 1                     
)
select *
    from (select t.row_id,
                 t.supplier_name,
                 t.tel,
                 address,
                 t.contact,
                 t.contact_post,
                 t.contact_mobi,
                 t.contact_tel,
                 case
                   when project_supp_type = 0 then
                    to_number(nvl(t.discount_rate, 0))
                   when project_supp_type2 = 0 then
                    to_number(nvl(t.discount_rate2, 0))
                   when project_supp_type3 = 0 then
                    to_number(nvl(t.discount_rate3, 0))
                   when project_supp_type4 = 0 then
                    to_number(nvl(t.discount_rate4, 0))
                   when project_supp_type5 = 0 then
                    to_number(nvl(t.discount_rate5, 0))
                   when project_supp_type6 = 0 then
                    to_number(nvl(t.discount_rate6, 0))
                 end as rate
            from md_supplier t
           where t.supplier_type = 4
             and (project_supp_type = 0 or project_supp_type2 = 0 or
                 project_supp_type3 = 0 or project_supp_type4 = 0 or
                 project_supp_type5 = 0 or project_supp_type6 = 0)
             and t.status = 1
             and exists (select * from ttt where supplier_id = t.row_id)
           order by rate desc, t.supplier_name)
   where rownum <= 5

View Code

 

sql运行时间为0.03秒

 

简单改写SQL达到优化目的

标签：