Oracle SQL基于时间条件排除特定行的优化方案咨询

之前的问题被关闭我无法理解，至少MT0正确理解了我的问题，说明描述没问题。MT0的方案存在缺陷：我的数据并非来自物理表，而是通过WITH子句多表关联、去重、分组查询得到的，执行AND t.ROWID = e.rid时会触发Oracle错误。

数据示例 #

生产库中大量行的end_date为DATE '9999-01-01'，代表订单处于开放状态（如brand ppp），数据示例如下：

CREATE TABLE table_name (brand, type, start_date, end_date) AS
  SELECT 'abc', 'W', DATE '2020-01-01', DATE '2020-06-30' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2020-07-01', DATE '2020-08-31' FROM DUAL UNION ALL
  SELECT 'abc', 'W', DATE '2020-09-01', DATE '2020-09-30' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'A', DATE '2023-07-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'W', DATE '2021-01-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'A', DATE '2019-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'W', DATE '2022-01-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'A', DATE '2023-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'A', DATE '2023-01-02', DATE '2023-09-30' FROM DUAL UNION ALL
  SELECT 'ppp', 'A', DATE '2023-01-01', DATE '9999-01-01' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2022-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2021-09-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2022-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2023-09-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'A', DATE '2023-01-02', DATE '2023-08-31' FROM DUAL;

需求说明 #

• 仅保留符合业务逻辑的type A行；
• type W行优先级最高，必须全部保留；若实在无法实现，保留有效的type A行也可；
• type A行无效判定：其时间范围全程被同brand的type W行覆盖，无单独运行时段。例如brand mmm、zzz的type A行全程被W覆盖，无效；brand qqq的A行被两段W行完全覆盖，无效；brand uuu的A行在2023-07-01至2023-08-31有单独运行时段，有效；
• MT0的方案对brand ppp的开放行处理正确，但未确保所有type W行被保留。

现有方案痛点 #

此前尝试的两种方案均存在问题：

• 逐天检查的PL/SQL混合方法效率极低；
• 按日期拆分的scaffolding方法会因开放行生成大量数据，耗尽临时表空间。

求助 #

现寻求针对3000万级数据量的优化解决方案。

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