where 函数问题引起执行计划改变（复杂视图）

最新推荐文章于 2020-07-23 05:04:47 发布

1ihj2015

最新推荐文章于 2020-07-23 05:04:47 发布

阅读量587

点赞数

分类专栏： Oracle Troubleshooting

本文链接：https://blog.csdn.net/itdba/article/details/37695205

版权

Oracle Troubleshooting 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

使用函数非常慢，使用变量很快
FROM v_hldtotal_price a,
       v_balance_price b,
       v_hlddetail_price c,
       v_symbol_price d
WHERE a.t_date = b.t_date
   AND a.t_date = c.t_date
   AND a.t_date = d.t_date
   AND a.f_code = b.f_code
   AND a.f_code = c.f_code
   AND a.f_code = d.f_code
   AND a.f_code = :fcode
   AND a.t_date = fun_date_start_last_end (:YEAR, :num, :style, :fcode)

修改办法：釜底抽薪
explain plan for   SELECT nvl (a.a701, 0) AS a701, nvl (a.a604, 0) AS a604,
       nvl (c.a100201, 0) AS a100201, nvl (d.bb, 0) AS bb,
       nvl (d.bc, 0) AS bc, nvl (d.fbd, 0) AS fbd,
       (nvl (b.a601102, 0) + nvl (b.a610103, 0)) AS zchzq, nvl (d.e, 0) AS e,
       nvl (d.feq, 0) AS feq, 0 other_qyzc, nvl (c.a2202, 0) AS a2202

FROM (select * from TYDW.V_HLDTOTAL_PRICE where T_date=TYDW.fun_date_start_last_end (:YEAR, :num, :style, :fcode))   a,
       (select * from TYDW.v_balance_price where T_date=TYDW.fun_date_start_last_end (:YEAR, :num, :style, :fcode)) b,
       (select * from TYDW.v_hlddetail_price where T_date=TYDW.fun_date_start_last_end (:YEAR, :num, :style, :fcode))   c,
       (select * from TYDW.v_symbol_price where T_date=TYDW.fun_date_start_last_end (:YEAR, :num, :style, :fcode)) d
WHERE a.t_date = b.t_date
   AND a.t_date = c.t_date
   AND a.t_date = d.t_date
   AND a.f_code = b.f_code
   AND a.f_code = c.f_code
   AND a.f_code = d.f_code
   AND a.f_code = :fcode