如何提高大型表的选择性能

Question

我现在有一个要花很长时间的过程(一个小时+-)。

这一过程基本上是这样做的：

首先，它从一张桌子连接到一个视图-

 SELECT * FROM 
 STG_CRM, V_CRM 
 WHERE
 STG_CRM.CRM_CASE_ID=V_CRM.CASE_ID(+)

DDL意见：

create or replace view stg_admin.v_crm as
select  t.case_id
from crm_case t, dim_crm x
where t.case_id=x.crm_case_id;

STG_CRM -200 k记录-没有索引。

DIM_CRM -90记录-索引(crm_case_id -唯一)。

CRM_CASE -200 k记录-没有索引。

直到现在，一切还没有太重(大约2-3分钟)，然后有一个左连接到另一个视图，这是目前最重的选择(只需从视图中选择*为10分钟)。

查看DDL --我目前正在考虑两个不同的查询：

select t.crm_case_id,s.customer_key
from stg_crm t, stg_scd s
where t.account_number=s.account_number
and t.case_create_date  between s.start_date and s.end_date;

或者：

select t.crm_case_id,
       (select min(s.customer_key) keep (dense_rank first order by s.end_date asc)
        from stg_scd s
        where t.account_number = s.account_number and
              t.case_create_date <= s.end_date 
       ) as customer_key
from stg_crm t

表stg_scd -5亿记录索引(customer_key，start_date，end_date) -唯一的分区由end_date日报。

现在，这两个查询都需要很长的时间，第二个查询需要更长的时间。我猜是因为它没有使用索引，因为start_date不是用来过滤的，但是我不知道如何添加它。

我的问题是:我怎样才能让它更快？如果我在STG_CRM上添加create_date索引，会有帮助吗？(我甚至不知道DBA是否允许at)，因为这是一个小表。

限制：

1. 我无法更改大表上的索引(STG_SCD)
2. 我可能能够在其他表上添加索引，但只给出了一个很好的理由，因为它会损害使用此表的其他进程的性能。

隐式连接语法是通过我的程序生成的，因此不需要注释.

提前谢谢！

第一个选择左加入第二个选择大约需要30-60分钟。

Answer 1

我已经解决了这个问题！

我错过了两件事：

1) stg_scd的统计信息，我们在将分区添加到表后禁用了它，并在添加每个分区之后忘记添加统计信息。

2)在stg_crm (account_number，case_create_date)上添加索引

谢谢你所有的努力:)

Answer 2

由于您正在从一个非常大的表中聚合数据(正如您在第二个查询选项中所做的那样)，您可能通过使用query_rewrite提前计算这些值的结果而获得一些性能好处。这里有一篇文章介绍了这个特性的使用-- rewriting

您还可以找到许多关于使用此功能的最佳实践的其他示例，它在优化数据仓库查询方面具有很高的价值。

祝好运!