Oracle SQL查询性能优化求助：单客户查询耗时4分钟

编写了如下Oracle SQL查询，查询单个客户结果需4分钟，尝试过分步执行脚本、借助v$sql_monitor排查等方法，均未找到有效优化方案，请求在保留原有业务逻辑的前提下提升执行效率。

WITH d_table AS (
    SELECT *
    FROM (
        SELECT *
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY min_fx_date ASC) AS rnk,
                   customer_id,
                   min_fx_date,
                   product,
                   balance_column
            FROM table_def
        )
        WHERE rnk = 1
    )
    WHERE min_fx_date > '01/01/2017'
),

tc_lo_product AS (
    SELECT a.customer_id, 
           a.currency, 
           b.product, 
           a.rep_date
    FROM table_port a
    LEFT JOIN table_product_names b
        ON a.ln_aim = b.product_name
),

if_lo AS (
    SELECT DISTINCT b.customer_id,
                    total_amount,
                    a.i_date,
                    x.product,
                    a.document_n,
                    a.pro,
                    b.close_date
    FROM table_full a
    LEFT JOIN (
        SELECT customer_id, 
               rep_date,
               FIRST_VALUE(close_date) OVER (PARTITION BY document_n ORDER BY rep_date DESC) AS close_date,
               currency,
               document_n
        FROM table_port
    ) b
        ON a.document_n = b.document_n
        AND a.i_date = LAST_DAY(b.rep_date)
    LEFT JOIN table_product_names x
        ON a.ln_aim = x.product_name
),

part3 AS (
    SELECT DISTINCT k.customer_id,
                    k.currency,
                    FIRST_VALUE(k.currency) OVER (PARTITION BY k.customer_id, k.product ORDER BY t.min_fx_date) AS main_currency,
                    t.min_fx_date,
                    k.product,
                    t.balance_column
    FROM d_table t
    LEFT JOIN tc_lo_product k
        ON t.customer_id = k.customer_id
        AND t.product = k.product
        AND k.rep_date = t.min_fx_date
),

kurs_cedvel AS (
    SELECT *
    FROM (
        SELECT c1.cur_short, 
               c1.k_v, 
               LAST_DAY(c1.p_date) AS rep_date, 
               ROW_NUMBER() OVER (PARTITION BY c1.cu_code, TO_CHAR(c1.p_date, 'MM-YYYY') ORDER BY c1.p_date DESC) AS rn
        FROM table_currency c1
        WHERE c1.kurs_type = 'CBAR'
    )
    WHERE rn = 1
),

in_rate_tab AS (
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, TRUNC(rep_date, 'MM') ORDER BY rep_date DESC, date_give DESC) AS rnk,
               rep_date,
               customer_id,
               interest_rate,
               date_give
        FROM table_port
        WHERE rep_date = LAST_DAY(rep_date)
          AND document_n NOT LIKE '%KOP%'
    )
    WHERE rnk = 1
),

ana_cedvel AS (
    SELECT p.*, 
           o.sum_payed,
           CASE
               WHEN main_currency = 'AZN' THEN p.total_amount
               ELSE ROUND(p.total_amount / v.k_v, 2)
           END AS total_amount_c,
           CASE
               WHEN main_currency = 'AZN' THEN p.pro
               ELSE ROUND(p.pro / v.kurs_val, 2)
           END AS pro_c,
           CASE
               WHEN main_currency = o.valyuta THEN o.sum_payed
               ELSE ROUND(o.sum_payed / v.k_v, 2)
           END AS sum_payed_c
    FROM (
        SELECT k.customer_id,
               k.currency,
               k.main_currency,
               k.min_fx_date,
               k.product,
               k.balance_column,
               c.total_amount,
               c.i_date,
               c.document_n,
               c.pro,
               c.close_date
        FROM part3 k
        LEFT JOIN if_lo c
            ON k.customer_id = c.customer_id
            AND k.product = c.product
            AND c.i_date >= k.min_fx_date
    ) p
    LEFT JOIN kurs_cedvel v
        ON p.main_currency = v.cur_short 
        AND p.i_date = v.rep_date
    LEFT JOIN payments_table o
        ON p.document_n = o.document_n
        AND o.payment_date >= p.min_fx_date
        AND p.i_date = LAST_DAY(o.payment_date)
),

final_result AS (
    SELECT DISTINCT h.i_date,
                    h.customer_id,
                    h.main_currency,
                    h.product,
                    h.min_fx_date,
                    h.balance_column,
                    h.total_amount,
                    h.pro,
                    h.sum_payed,
                    h.interest_rate,
                    CASE
                        WHEN h.max_i_date = LAST_DAY(h.close_date)
                             AND h.i_date = h.max_i_date THEN h.close_date
                        ELSE NULL
                    END AS date_end
    FROM (
        SELECT DISTINCT i_date,
                        j.customer_id,
                        main_currency,
                        MAX(i_date) OVER (PARTITION BY j.customer_id, j.product) AS max_i_date,
                        j.document_n,
                        product,
                        min_fx_date,
                        balance_column,
                        p.interest_rate,
                        close_date,
                        SUM(total_amount_c) OVER (PARTITION BY i_date, j.customer_id, product) AS total_amount,
                        SUM(sum_payed_c) OVER (PARTITION BY i_date, j.customer_id, product) AS sum_payed,
                        SUM(pro) OVER (PARTITION BY i_date, j.customer_id, product) AS pro
        FROM ana_cedvel j
        LEFT JOIN in_rate_tab p
            ON j.customer_id = p.customer_id
            AND j.i_date = p.rep_date
    ) h
    WHERE h.customer_id = 85278945789
)

优化建议 #

1. 尽早过滤数据，缩小数据集 #

• 将d_table中的日期过滤条件提前到最内层查询，避免先执行窗口函数再过滤：

  d_table AS (
      SELECT customer_id, min_fx_date, product, balance_column
      FROM (
          SELECT ROW_NUMBER() OVER (PARTITION BY customer_id, product ORDER BY min_fx_date ASC) AS rnk,
                 customer_id, min_fx_date, product, balance_column
          FROM table_def
          WHERE min_fx_date > DATE '2017-01-01' -- 用DATE类型避免隐式转换
      )
      WHERE rnk = 1
  )
  

• 把最终查询的customer_id = 85278945789提前到所有涉及客户的CTE中（比如d_table、tc_lo_product、if_lo），直接过滤单个客户的数据，减少每个CTE的处理量。

2. 优化窗口函数与去重逻辑 #

• 多个CTE同时使用DISTINCT和窗口函数，检查是否可以通过调整窗口函数的分区规则去掉DISTINCT，比如part3中，若(customer_id, product, min_fx_date)是唯一的，DISTINCT可以直接移除。
• if_lo中的子查询用FIRST_VALUE获取最新close_date，可以替换为ROW_NUMBER()过滤，减少窗口函数的计算开销：

  SELECT customer_id, rep_date, close_date, currency, document_n
  FROM (
      SELECT customer_id, 
             rep_date,
             close_date,
             currency,
             document_n,
             ROW_NUMBER() OVER (PARTITION BY document_n ORDER BY rep_date DESC) AS rn
      FROM table_port
  )
  WHERE rn = 1
  

3. 针对性创建索引 #

根据查询的过滤、关联、窗口函数条件，创建覆盖索引减少表扫描：

• table_def：CREATE INDEX idx_table_def_cust_prod_date ON table_def(customer_id, product, min_fx_date) INCLUDE (balance_column);
• table_port：
  • 针对if_lo子查询：CREATE INDEX idx_table_port_doc_rep ON table_port(document_n, rep_date DESC) INCLUDE (customer_id, close_date, currency);
  • 针对in_rate_tab：CREATE INDEX idx_table_port_cust_rep ON table_port(customer_id, rep_date DESC, date_give DESC) INCLUDE (interest_rate, document_n);
• table_full：CREATE INDEX idx_table_full_doc_date ON table_full(document_n, i_date) INCLUDE (total_amount, pro, ln_aim);
• payments_table：CREATE INDEX idx_payments_doc_date ON payments_table(document_n, payment_date) INCLUDE (sum_payed, valyuta);
• table_currency：CREATE INDEX idx_currency_type_code_month ON table_currency(kurs_type, cu_code, TO_CHAR(p_date, 'MM-YYYY'), p_date DESC) INCLUDE (cur_short, k_v);

4. 消除隐式数据类型转换 #

• 日期比较时使用DATE '2017-01-01'而非字符串，避免Oracle自动转换导致索引失效。
• 检查所有关联条件的字段类型，确保i_date、rep_date等日期字段类型一致，避免转换开销。

5. 简化CTE嵌套结构 #

• d_table原本有三层嵌套，可简化为两层，减少不必要的子查询开销（如上述优化点1中的示例）。

内容的提问来源于stack exchange，提问作者Narin