Oracle创建BIRTHDAY_SALES表查询运行过慢，求优化方案

问题背景 #

尝试创建BIRTHDAY_SALES表时，初始查询运行8小时未完成；添加/*+ parallel(32) */并行提示并将时间范围限定为2022年后，运行3小时仍未结束。原查询如下：

DROP TABLE BIRTHDAY_SALES;
CREATE TABLE BIRTHDAY_SALES AS
SELECT  /*+ parallel(32) */ 
    DISTINCT T.CONTACT_KEY
,   S.CAMPAIGN_NAME
,   S.CONTROL_GROUP_FLAG
,   S.SEGMENT_NAME
,   count(distinct t.ORDER_NUM) as TRANS   
,   count(distinct case when p.store_key = '42381' then t.ORDER_NUM else NULL end) as TRANS_ONLINE         
,   count(distinct case when p.store_key != '42381' then t.ORDER_NUM else NULL end) as TRANS_OFFLINE   
,   sum(t.ITEM_AMT) as SALES
,   sum(case when p.store_key = '42381' then t.ITEM_AMT else NULL end) as SALES_ONLINE         
,   sum(case when p.store_key != '42381' then t.ITEM_AMT else NULL end) as SALES_OFFLINE           
,   sum(case when t.item_quantity_val>0 and t.item_amt<=0 then 0 else t.item_quantity_val end) QTY    
,   sum(case when (p.store_key = '42381' and t.ITEM_QUANTITY_VAL>0 and t.ITEM_AMT>0) then t.ITEM_QUANTITY_VAL else null end) QTY_ONLINE           
,   sum(case when (p.store_key != '42381' and t.ITEM_QUANTITY_VAL>0 and t.ITEM_AMT>0) then t.ITEM_QUANTITY_VAL else null end) QTY_OFFLINE       
FROM CRM_TARGET.B_TRANSACTION T
JOIN BDAY_PROG S 
ON T.CONTACT_KEY = S.CONTACT_KEY
JOIN CRM_TARGET.T_ORDITEM_SD P
ON T.PRODUCT_KEY = P.PRODUCT_KEY
WHERE t.TRANSACTION_TYPE_NAME = 'Item'
  AND t.BU_KEY = '15' 
  AND t.TRANSACTION_DT_KEY >= '20220101'
  AND t.TRANSACTION_DT_KEY <= '20221231' 
  AND t.member_sale_flag = 'Y'
  AND t.bu_key = '15'       
  AND t.CONTACT_KEY != 0           
GROUP BY 
    T.CONTACT_KEY
,   S.CAMPAIGN_NAME
,   S.CONTROL_GROUP_FLAG
,   S.SEGMENT_NAME;

核心优化措施 #

1. 移除冗余的DISTINCT #

原查询中SELECT后的DISTINCT完全多余——GROUP BY已经对分组字段做了去重，保留DISTINCT会额外增加计算开销，直接删除即可。

2. 优化索引策略 #

• 给CRM_TARGET.B_TRANSACTION创建复合过滤+连接+分组索引，覆盖查询所需所有字段，避免回表：

  CREATE INDEX IDX_B_TRANSACTION_FILTER ON CRM_TARGET.B_TRANSACTION 
  (BU_KEY, TRANSACTION_TYPE_NAME, member_sale_flag, TRANSACTION_DT_KEY, CONTACT_KEY, PRODUCT_KEY)
  INCLUDE (ORDER_NUM, ITEM_AMT, ITEM_QUANTITY_VAL);
  

• 给BDAY_PROG创建CONTACT_KEY的唯一索引（若不存在）：

  CREATE UNIQUE INDEX IDX_BDAY_PROG_CONTACT ON BDAY_PROG (CONTACT_KEY);
  

• 给CRM_TARGET.T_ORDITEM_SD创建包含store_key的PRODUCT_KEY索引：

  CREATE INDEX IDX_T_ORDITEM_SD_PROD ON CRM_TARGET.T_ORDITEM_SD (PRODUCT_KEY) INCLUDE (store_key);
  

3. 调整并行度与执行计划提示 #

• 并行度并非越高越好，32可能超出服务器资源上限，建议根据CPU核心数调整为parallel(8)或parallel(16)，避免资源争抢。
• 添加连接顺序提示，让小表优先参与连接，减少中间结果集：

  /*+ parallel(16) leading(T S P) use_hash(S P) */
  

4. 提前聚合，减少连接数据量 #

原查询先做三表连接再聚合会产生大量中间数据，可先对B_TRANSACTION预聚合，再与其他表连接：

DROP TABLE BIRTHDAY_SALES;
CREATE TABLE BIRTHDAY_SALES AS
SELECT /*+ parallel(16) */
    T_AGG.CONTACT_KEY
,   S.CAMPAIGN_NAME
,   S.CONTROL_GROUP_FLAG
,   S.SEGMENT_NAME
,   T_AGG.TRANS
,   sum(case when p.store_key = '42381' then T_AGG.ORDER_NUM_CNT else 0 end) as TRANS_ONLINE         
,   sum(case when p.store_key != '42381' then T_AGG.ORDER_NUM_CNT else 0 end) as TRANS_OFFLINE   
,   T_AGG.SALES
,   sum(case when p.store_key = '42381' then T_AGG.ITEM_AMT_SUM else 0 end) as SALES_ONLINE         
,   sum(case when p.store_key != '42381' then T_AGG.ITEM_AMT_SUM else 0 end) as SALES_OFFLINE           
,   T_AGG.QTY
,   sum(case when (p.store_key = '42381') then T_AGG.QTY_VAL else 0 end) as QTY_ONLINE           
,   sum(case when (p.store_key != '42381') then T_AGG.QTY_VAL else 0 end) as QTY_OFFLINE       
FROM (
    SELECT 
        CONTACT_KEY,
        PRODUCT_KEY,
        count(distinct ORDER_NUM) as TRANS,
        count(distinct ORDER_NUM) as ORDER_NUM_CNT,
        sum(ITEM_AMT) as SALES,
        sum(ITEM_AMT) as ITEM_AMT_SUM,
        sum(case when item_quantity_val>0 and item_amt<=0 then 0 else item_quantity_val end) QTY,
        sum(case when ITEM_QUANTITY_VAL>0 and ITEM_AMT>0 then ITEM_QUANTITY_VAL else 0 end) QTY_VAL
    FROM CRM_TARGET.B_TRANSACTION
    WHERE TRANSACTION_TYPE_NAME = 'Item'
      AND BU_KEY = '15' 
      AND TRANSACTION_DT_KEY >= '20220101'
      AND TRANSACTION_DT_KEY <= '20221231' 
      AND member_sale_flag = 'Y'
      AND CONTACT_KEY != 0
    GROUP BY CONTACT_KEY, PRODUCT_KEY
) T_AGG
JOIN BDAY_PROG S 
ON T_AGG.CONTACT_KEY = S.CONTACT_KEY
JOIN CRM_TARGET.T_ORDITEM_SD P
ON T_AGG.PRODUCT_KEY = P.PRODUCT_KEY
GROUP BY 
    T_AGG.CONTACT_KEY
,   S.CAMPAIGN_NAME
,   S.CONTROL_GROUP_FLAG
,   S.SEGMENT_NAME;

5. 更新统计信息 #

执行以下语句更新三张表的统计信息，让优化器生成更优执行计划：

EXEC DBMS_STATS.GATHER_TABLE_STATS('CRM_TARGET', 'B_TRANSACTION', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('CRM_TARGET', 'T_ORDITEM_SD', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA', 'BDAY_PROG', CASCADE => TRUE); -- 替换为BDAY_PROG的实际Schema

6. 简化CASE表达式 #

以QTY计算为例，可简化逻辑减少计算步骤（需确认业务逻辑等价）：

sum(CASE WHEN item_quantity_val > 0 AND item_amt > 0 THEN item_quantity_val ELSE 0 END) QTY

其他建议 #

• 避开业务高峰时段执行查询，减少资源竞争。
• 若BIRTHDAY_SALES无需长期保留，可创建为全局临时表，利用临时表存储优化。

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