Oracle SQL按业务日期取最新版本数据的性能优化请求

业务流程 #

• 每日多次从上游系统接收价格数据
• 管理员可直接审批（不递增VERSION）或修改价格后审批
• 管理员可对已审批数据在1年内修正价格，用于审计或对账

问题背景 #

ITEM_PRICE表存储了13个月的数百万条记录，每日需拉取约2万条按BUSINESS_DT分组的最新VERSION数据。现有查询能得到预期结果，但性能表现较差。

需求 #

提供更高效的Oracle SQL优化方案，替代当前查询逻辑。已提供样例数据及现有查询。

现有查询代码 #

WITH LATEST_ITEMS AS (
  SELECT ORIGINAL.ITEM_ID, ORIGINAL.ITEM_SUB_ID, ORIGINAL.BUSINESS_DT,ORIGINAL.REPORT_DT,
  ORIGINAL.VERSION, ORIGINAL.STATE, ORIGINAL.PRICE FROM ITEM_PRICE ORIGINAL
  JOIN (
    SELECT ITEM_ID, ITEM_SUB_ID, BUSINESS_DT, MAX(VERSION) MAX_VERSION FROM ITEM_PRICE 
    WHERE STATE IN ('RECEIVED', 'APPROVED') 
    GROUP BY ITEM_ID, ITEM_SUB_ID, BUSINESS_DT) 
      MAX_VERSION
  ON NVL(MAX_VERSION.ITEM_SUB_ID,0) = NVL(ORIGINAL.ITEM_SUB_ID,0)
  AND MAX_VERSION.ITEM_ID = ORIGINAL.ITEM_ID
  AND TRUNC(MAX_VERSION.BUSINESS_DT) = TRUNC(ORIGINAL.BUSINESS_DT)
  AND TRUNC(MAX_VERSION.MAX_VERSION) = TRUNC(ORIGINAL.VERSION)
),
MAX_WITH_CORRECTED AS (
  SELECT ORIGINAL.ITEM_ID, ORIGINAL.ITEM_SUB_ID, ORIGINAL.BUSINESS_DT,ORIGINAL.REPORT_DT,
  ORIGINAL.VERSION, ORIGINAL.STATE, ORIGINAL.PRICE FROM ITEM_PRICE ORIGINAL
  JOIN (
    SELECT ITEM_ID, ITEM_SUB_ID, BUSINESS_DT, MAX(VERSION) MAX_VERSION FROM ITEM_PRICE 
    GROUP BY ITEM_ID, ITEM_SUB_ID, BUSINESS_DT) 
      MAX_VERSION
  ON NVL(MAX_VERSION.ITEM_SUB_ID,0) = NVL(ORIGINAL.ITEM_SUB_ID,0)
  AND MAX_VERSION.ITEM_ID = ORIGINAL.ITEM_ID
  AND TRUNC(MAX_VERSION.BUSINESS_DT) = TRUNC(ORIGINAL.BUSINESS_DT)
  AND TRUNC(MAX_VERSION.MAX_VERSION) = TRUNC(ORIGINAL.VERSION)
)

SELECT MAX_WITH_CORRECTED.* FROM MAX_WITH_CORRECTED INNER JOIN LATEST_ITEMS 
ON NVL(MAX_WITH_CORRECTED.ITEM_SUB_ID,0) = NVL(LATEST_ITEMS.ITEM_SUB_ID,0)
  AND MAX_WITH_CORRECTED.ITEM_ID = LATEST_ITEMS.ITEM_ID
  AND TRUNC(MAX_WITH_CORRECTED.BUSINESS_DT) = TRUNC(LATEST_ITEMS.BUSINESS_DT)
  AND TRUNC(MAX_WITH_CORRECTED.BUSINESS_DT) = '15-AUG-2023'
;

样例数据 #

CREATE TABLE ITEM_PRICE
       (ITEM_ID NUMBER(10), ITEM_SUB_ID NUMBER(10), BUSINESS_DT DATE, REPORT_DT DATE,
        VERSION NUMBER(3), STATE VARCHAR2(10), PRICE NUMBER(7,2)) ;

INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '15-AUG-2023', 1, 'RECEIVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '15-AUG-2023', 2, 'APPROVED', 20 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1001, '15-AUG-2023', '15-AUG-2023', 1, 'RECEIVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1001, '15-AUG-2023', '15-AUG-2023', 2, 'APPROVED', 20 );
INSERT INTO ITEM_PRICE VALUES ( 101, NULL, '15-AUG-2023', '15-AUG-2023', 1, 'APPROVED', 10 );

INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 1, 'RECEIVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 2, 'RECEIVED', 20 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 3, 'APPROVED', 30 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '16-AUG-2023', '16-AUG-2023', 4, 'CORRECTED', 50 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1001, '16-AUG-2023', '16-AUG-2023', 1, 'APPROVED', 10 );
INSERT INTO ITEM_PRICE VALUES ( 101, NULL, '16-AUG-2023', '16-AUG-2023', 1, 'RECEIVED', 10 );

INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '16-AUG-2023', 3, 'CORRECTED', 15 );
INSERT INTO ITEM_PRICE VALUES ( 100, 1000, '15-AUG-2023', '16-AUG-2023', 4, 'CORRECTED', 18 );
INSERT INTO ITEM_PRICE VALUES ( 101, NULL, '15-AUG-2023', '16-AUG-2023', 2, 'CORRECTED', 20 );

优化方案 #

核心优化思路 #

1. 减少表扫描次数：原查询两次全表扫描获取分组最大版本，合并逻辑为单次扫描，降低IO开销。
2. 消除函数对索引的抑制：避免在关联条件中对BUSINESS_DT和VERSION使用TRUNC，若字段含时分秒，可新增冗余字段或创建函数索引。
3. 用窗口函数替代JOIN分组：利用OracleROW_NUMBER()窗口函数直接标记每组最新数据，避免多表JOIN的性能损耗。
4. 提前过滤数据：在子查询阶段先筛选目标BUSINESS_DT，减少后续处理的数据量。

优化后的SQL代码 #

SELECT ip.ITEM_ID, ip.ITEM_SUB_ID, ip.BUSINESS_DT, ip.REPORT_DT,
       ip.VERSION, ip.STATE, ip.PRICE
FROM (
    SELECT 
        ip.*,
        -- 标记全量状态下每组的最新版本
        ROW_NUMBER() OVER (
            PARTITION BY ip.ITEM_ID, NVL(ip.ITEM_SUB_ID, 0), TRUNC(ip.BUSINESS_DT)
            ORDER BY ip.VERSION DESC
        ) AS rn_all,
        -- 标记仅RECEIVED/APPROVED状态下每组的最新版本
        ROW_NUMBER() OVER (
            PARTITION BY ip.ITEM_ID, NVL(ip.ITEM_SUB_ID, 0), TRUNC(ip.BUSINESS_DT)
            ORDER BY CASE WHEN ip.STATE IN ('RECEIVED', 'APPROVED') THEN ip.VERSION ELSE 0 END DESC
        ) AS rn_approved
    FROM ITEM_PRICE ip
    WHERE TRUNC(ip.BUSINESS_DT) = '15-AUG-2023'
) ip
-- 筛选符合原查询逻辑的记录：全量最新版本，且对应分组存在有效审批/接收记录
WHERE rn_all = 1 
  AND rn_approved = 1
  AND EXISTS (
      SELECT 1 
      FROM ITEM_PRICE ip2
      WHERE ip2.ITEM_ID = ip.ITEM_ID
        AND NVL(ip2.ITEM_SUB_ID, 0) = NVL(ip.ITEM_SUB_ID, 0)
        AND TRUNC(ip2.BUSINESS_DT) = TRUNC(ip.BUSINESS_DT)
        AND ip2.STATE IN ('RECEIVED', 'APPROVED')
  );

额外性能建议 #

• 创建复合覆盖索引：CREATE INDEX IDX_ITEM_PRICE_GROUP ON ITEM_PRICE(TRUNC(BUSINESS_DT), ITEM_ID, NVL(ITEM_SUB_ID, 0), VERSION, STATE, PRICE);，直接覆盖查询所需字段，避免回表。
• 函数索引优化：若BUSINESS_DT含时分秒，创建CREATE INDEX IDX_ITEM_PRICE_TRUNC_BUSINESS ON ITEM_PRICE(TRUNC(BUSINESS_DT));加速日期过滤。
• 分区表改造：按BUSINESS_DT创建范围分区，每日查询仅扫描对应分区，大幅减少数据扫描量。

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