sql - 如何优化 View ？

Question

以下 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;

Answer 1

不知道您的表结构和最少的示例数据(请编辑您的帖子并向我们展示!)，我会尝试解决以下问题:

查看 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;