sql - 如何优化 View ？

以下视图在很长一段时间后返回结果。如何优化以获得快速结果？

CREATE OR REPLACE VIEW v_e_soa
  AS
    SELECT substr(account_id,1,16) account_id,
           stmt_on_email,
           MIN(curr_no) curr_no,
           co_code,
           ( SELECT convert_to_date(MIN(date_time) )
             FROM ext_account_his_31122017 y
             WHERE y.stmt_on_email = x.stmt_on_email
                   AND   substr(y.account_id,1,16) = substr(x.account_id,1,16)
           ) date_time
    FROM ext_account_his_31122017 x
    WHERE stmt_on_email = 'YES'
    GROUP BY substr(account_id,1,16),
             stmt_on_email,
             co_code;

最佳答案

我不知道您的表结构和最少的示例数据（请编辑您的文章并向我们展示！），我会尽量保留以下几点：


查看substr（account_id），在位置16之后截断数字的原因是什么？account_id是VARCHAR2还是NUMBER？
您在stmt_on_email ='YES'上进行过滤。这可能可以在更早的阶段完成（例如，使用WITH子句）。没有理由在GROUP BY子句中包含它。
最大的收益是SEELCT convert_to_date子句中的概率。它需要表的自连接，并为每一行调用一个函数。两者可能都是不必要的。


我会尝试以下方法：

WITH
filtered_stmt_yes AS (
  SELECT substr(account_id,1,16) account_id,
         stmt_on_email,
         curr_no,
         co_code,
         to_date(date_time,'YYYYMMDDHH24MI') as date_time
    FROM ext_account_his_31122017 x
   WHERE stmt_on_email = 'YES'
   ORDER BY account_id),
ranked AS (
  SELECT account_id, stmt_on_email, curr_no, co_code, date_time,
         min(curr_no) over (partition by account_id, co_code) as min_curr_no,
         min(date_time) over (partition by account_id) as min_date_time,
         row_number() over (partition by account_id order by rowid) as r
    FROM filtered_stmt_yes)
SELECT account_id,
       stmt_on_email,
       min_curr_no as cur_no,
       co_code,
       min_date_time as date_time
  FROM ranked
 WHERE r=1;

关于sql - 如何优化 View ？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50986327/