简单改写SQL达到优化目的

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 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秒