oracle11g 一条几百行行的sql语句 优化 需要适可而止才行

1，同事报告了一条非常复杂的sql，说很慢，叫我优化下。

很慢的sql，也很长有几百行吧，^_^，如下：

select bm2.bis_must_id,

       bm2.qz_year_month,

       bm2.money,

       bm2.adjust_money,

       bm2.bis_cont_id,

       bm2.must_type,

       bm2.rent_last_pay_date,

       bc.BIS_STORE_IDS,

       bc.BIS_STORE_NOS,

       bc.BIS_SHOP_ID,

       bc.BIS_SHOP_NAME,

       bc.BIS_PROJECT_ID,

       bp.short_name,

       bp.project_name,

       bm2.billing_period_begin,

       bm2.billing_period_end

  from (selectsum(m.money) money,

               sum(aj.adjust_money) adjust_money,

               m.must_type must_type,

               m.qz_year_month qz_year_month,

               m.bis_project_id bis_project_id,

               m.bis_cont_id bis_cont_id,

               max(m.rent_last_pay_date) rent_last_pay_date,

               max(m.billing_period_begin) billing_period_begin,

               min(m.billing_period_end) billing_period_end,

               max(m.bis_must_id) bis_must_id

          from bis_must2 m,

               (select j.*

                  from bis_mf_adjust j

                 where (j.is_del = '1'or j.is_del isnull)

                   and j.adjust_type = '1'

                   and j.fee_type = 1) aj

         where1 = 1

           and m.bis_project_id = '402834702b8b7fc8012b99606d320f1c'

           and m.must_type = 1-- 1租金  2物管

           and m.rent_last_pay_date isnotnull

           and m.rent_last_pay_date < SYSDATE

           and (m.is_delete = '0'or m.is_delete isnull) --不是删除的

           and (m.is_bd_show = '1'or m.is_bd_show isnull)

           and m.bis_cont_id = aj.bis_cont_id(+)

           and m.billing_period_begin = aj.billing_period_begin(+)

           and m.billing_period_end = aj.billing_period_end(+)

         groupby m.bis_project_id,

                  m.qz_year_month,

                  m.must_type,

                  m.bis_cont_id) bm2,

       bis_cont bc,

       bis_project bp

 where bm2.bis_cont_id = bc.bis_cont_id

   and bm2.bis_project_id = bp.bis_project_id

   and bc.store_type in (1,2)

   and bc.cont_type_cd in (1,2)

   and bc.status_cd in (1,2,3)

unionall

select bm2.bis_must_id,

       bm2.qz_year_month,

       bm2.money,

       bm2.adjust_money,

       bm2.bis_cont_id,

       bm2.must_type,

       bm2.rent_last_pay_date,

       bc.BIS_STORE_IDS,

       bc.BIS_STORE_NOS,

       bc.BIS_SHOP_ID,

       bc.BIS_SHOP_NAME,

       bc.BIS_PROJECT_ID,

       bp.short_name,

       bp.project_name,

       bm2.billing_period_begin,

       bm2.billing_period_end

  from (selectsum(m.money) money,

               sum(aj.adjust_money) adjust_money,

               m.must_type must_type,

               m.qz_year_month qz_year_month,

               m.bis_project_id bis_project_id,

               m.bis_cont_id bis_cont_id,

               max(m.rent_last_pay_date) rent_last_pay_date,

               max(m.billing_period_begin) billing_period_begin,

               min(m.billing_period_end) billing_period_end,

               max(m.bis_must_id) bis_must_id

          from bis_must2 m,

               (select j.*

                  from bis_mf_adjust j

                 where (j.is_del = '1'or j.is_del isnull)

                   and j.adjust_type = '1'

                   and j.fee_type = 2) aj

         where1 = 1

           and m.bis_project_id = '402834702b8b7fc8012b99606d320f1c'

           and m.must_type = 2-- 1租金  2物管

           and m.rent_last_pay_date isnotnull

           and m.rent_last_pay_date < SYSDATE

           and (m.is_delete = '0'or m.is_delete isnull) --不是删除的

           and (m.is_bd_show = '1'or m.is_bd_show isnull)

           and m.bis_cont_id = aj.bis_cont_id(+)

           and m.billing_period_begin = aj.billing_period_begin(+)

           and m.billing_period_end = aj.billing_period_end(+)

         groupby m.bis_project_id,

                  m.qz_year_month,

                  m.must_type,

                  m.bis_cont_id) bm2,

       bis_cont bc,

       bis_project bp

 where bm2.bis_cont_id = bc.bis_cont_id

   and bm2.bis_project_id = bp.bis_project_id

   and bc.store_type in (1,2)

   and bc.cont_type_cd in (1,2)

   and bc.status_cd in (1,2,3) 

 

 

 

 

2，F5分析

F5分析后，发现有几个table是表扫描，估计问题出在这里，比如bis_must2、bis_mf_adjust这两个表，先优化bis_must2表，根据where后面的条件，查看下比例分析结果02.png：

 

看到数据比较散漫数据不是很集中，可以在这上面建立索引以提高查询效率。

 

3，添加索引

-- Create/Recreate indexes

createindex IDX_PROJECT_ID on BIS_MUST2 (bis_project_id, must_type);                   

 

 

再F5执行查看计划分析，看到COST已经降下来了，如下图01.png：

 

4，查看优化效果

再新打开一个plsql查询窗口，看到执行效率很好，0.1秒就OK了，如04.png所示：

 

5，优化适可而止

“F5分析后，发现有几个table是表扫描，估计问题出在这里，比如bis_must2、bis_mf_adjust这两个表，先优化bis_must2表”

 

优化只要达到目标后，就可以了，不需要再继续优化，因为Oracle比较复杂，再添加多余的索引也会影响oracle的dml性能，所以优化了第一个表bis_must2的索引达到了效果后，就可以适可而止了。