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

来源：互联网发布：郑州市大数据产业园编辑：程序博客网时间：2024/06/05 08:15

使用函数非常慢，使用变量很快
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

0 0