sql - 在任何其他 SQL 之前先执行子查询重构

Question

我有一个非常复杂的 View ，其形式如下

create or replace view loan_vw as 
select * from (with loan_info as (select loan_table.*,commission_table.* 
                                   from loan_table,
                                  commission_table where 
                                  contract_id=commission_id)
                select /*complex transformations */ from loan_info
                where type <> 'PRINCIPAL'
                union all 
                select /*complex transformations */ from loan_info
                where type = 'PRINCIPAL')

现在，如果我执行以下操作，请选择查询挂起

         select * from loan_vw where contract_id='HA001234TY56';

但是如果我在子查询重构中硬编码或在同一个 session 中使用包级变量，查询将在一秒钟内返回

create or replace view loan_vw as 
        select * from (with loan_info as (select loan_table.*,commission_table.* 
                                           from loan_table,
                                          commission_table where 
                                          contract_id=commission_id
                                          and contract_id='HA001234TY56'
                                          )
                        select /*complex transformations */ from loan_info
                        where type <> 'PRINCIPAL'
                        union all 
                        select /*complex transformations */ from loan_info
                        where type = 'PRINCIPAL')

由于我使用业务对象，我不能使用包级别变量

所以我的问题是Oracle中有一个提示告诉优化器在子查询重构中首先检查loan_vw中的contract_id

根据要求，使用的分析函数如下

select value_date, item, credit_entry, item_paid
from (
  select value_date, item, credit_entry, debit_entry,
    greatest(0, least(credit_entry, nvl(sum(debit_entry) over (), 0)
      - nvl(sum(credit_entry) over (order by value_date
          rows between unbounded preceding and 1 preceding), 0))) as item_paid
  from your_table
)
where item is not null;

在遵循 Boneist 和 MarcinJ 给出的建议后，我删除了子查询重构 (CTE) 并编写了一个长查询，如下所示，将性能从 3 分钟提高到 0.156 秒

  create or replace view loan_vw as
  select /*complex transformations */
                               from loan_table,
                              commission_table where 
                              contract_id=commission_id
               and loan_table.type <> 'PRINCIPAL'
  union all
  select /*complex transformations */
                               from loan_table,
                              commission_table where 
                              contract_id=commission_id
               and loan_table.type = 'PRINCIPAL'

Answer 1

这些转换真的那么复杂，您必须使用UNION ALL ?优化您看不到的东西真的很难，但是您是否尝试过摆脱 CTE 并在线实现计算？

CREATE OR REPLACE VIEW loan_vw AS
SELECT loan.contract_id
     , CASE commission.type -- or wherever this comes from
         WHEN 'PRINCIPAL'
         THEN SUM(whatever) OVER (PARTITION BY loan.contract_id, loan.type) -- total_whatever

         ELSE SUM(something_else) OVER (PARTITION BY loan.contract_id, loan.type) -- total_something_else
      END AS whatever_something
  FROM loan_table loan 
 INNER 
  JOIN commission_table commission
    ON loan.contract_id = commission.commission_id

请注意，如果您的分析函数没有 PARTITION BY contract_id你将无法在 contract_id 上使用索引列。

Take a look at this db fiddle (您必须单击最后一个结果表上的 ... 以展开结果)。在这里， loan表有一个索引 (PK) contract_id专栏，还要 some_other_id这也是唯一的，但没有索引，并且外部查询的谓词仍在 contract_id 上.如果您比较 partition by contract 的计划和 partition by other id ，您会看到在 partition by other id 中根本没有使用索引计划:有一个 TABLE ACCESS与 FULL与 INDEX 相比，贷款表上的选项- UNIQUE SCAN在 partition by contract .那显然是因为优化器无法解析 contract_id 之间的关系。和 some_other_id由它自己，所以它需要运行 SUM或 AVG在整个窗口上，而不是通过索引使用限制窗口行数。

您还可以尝试 - 如果您有包含这些契约(Contract)的维度表 - 将其加入您的结果并公开 contract_id从维度表而不是最有可能的巨额贷款事实表。有时，这可以通过在维度表上使用唯一索引来改进基数估计。

同样，在没有查询甚至计划的情况下，优化黑盒真的很难，所以我们不知道发生了什么。例如，CTE 或子查询可能会不必要地具体化。