Oracle 19c创建含OUTER APPLY的物化视图报ORA-00907缺失右括号错误

数据老陈

2026-8-26

问题现象

在Oracle 19c版本数据库中创建物化视图时，抛出如下报错：

ORA-00907: missing right parenthesis

报错触发场景为使用OUTER APPLY语法实现汇率按日匹配逻辑：针对2022-02-10至当前日期的每一天，当日存在最新创建的CURRRENCY_RATES记录则取当日记录，当日无对应记录则取距离当日最近的历史日期创建的记录。原创建SQL如下：

CREATE MATERIALIZED VIEW "MV_LAST_CREATED_RATE_PER_DAY" 
("DAY_ID", "CURRRENCY_RATE_ID", "FROM_CURRRENCY_ID", "TO_CURRRENCY_ID", "RATE", "VALIDITY_DATE", "CREATE_DATE")
  BUILD IMMEDIATE
  REFRESH FORCE ON DEMAND
  AS   
  WITH DAYS AS (
      select TO_NUMBER (TO_CHAR (date'2022-02-10' + level - 1, 'yyyymmdd')) DAY_ID
      from   dual
      connect by level <= (
          sysdate - date'2022-02-10' + 1
      )
  ),
  LAST_CREATED_RATE_ON_DAY AS (
      SELECT *
         FROM (
            SELECT TO_NUMBER (TO_CHAR (CREATE_DATE, 'yyyymmdd')) as DAY_ID,
                   RATES.*,
                   RANK ()
                      OVER (
                         PARTITION BY TO_NUMBER (TO_CHAR (CREATE_DATE, 'yyyymmdd')),
                                 FROM_CURRRENCY_ID,
                                 TO_CURRRENCY_ID
                          ORDER BY CURRRENCY_RATE_ID DESC)
                                                      AS RNK
            FROM CURRRENCY_RATES RATES)
      WHERE RNK = 1
   )
   SELECT d.DAY_ID,
          lcrod.CURRRENCY_RATE_ID,
          lcrod.FROM_CURRRENCY_ID,
          lcrod.TO_CURRRENCY_ID,
          lcrod.RATE,
          lcrod.VALIDITY_DATE,
       lcrod.CREATE_DATE
   FROM DAYS d
   OUTER APPLY (
        SELECT * 
        FROM LAST_CREATED_RATE_ON_DAY lcrod
        WHERE lcrod.DAY_ID <= d.DAY_ID
        ORDER BY lcrod.DAY_ID DESC
        FETCH NEXT 1 ROWS ONLY
   ) lcrod;

问题根因

Oracle 19c物化视图的查询解析器不支持在关联子查询中使用OUTER APPLY搭配FETCH NEXT ... ROWS ONLY的写法，解析过程会误判括号匹配关系，最终抛出缺失右括号的错误。该语法在普通SELECT查询中可正常执行，但在物化视图定义中属于未兼容的语法组合。

兼容改写方案

改用窗口函数+左关联的写法实现相同逻辑，完全规避语法限制，同时执行效率更高，改写后的SQL可直接创建成功：

CREATE MATERIALIZED VIEW "MV_LAST_CREATED_RATE_PER_DAY" 
("DAY_ID", "CURRRENCY_RATE_ID", "FROM_CURRRENCY_ID", "TO_CURRRENCY_ID", "RATE", "VALIDITY_DATE", "CREATE_DATE")
  BUILD IMMEDIATE
  REFRESH FORCE ON DEMAND
  AS   
WITH DAYS AS (
    SELECT TO_NUMBER(TO_CHAR(date'2022-02-10' + level - 1, 'yyyymmdd')) DAY_ID
    FROM dual
    CONNECT BY level <= (sysdate - date'2022-02-10' + 1)
),
RATE_WITH_DAYID AS (
    SELECT 
        TO_NUMBER(TO_CHAR(CREATE_DATE, 'yyyymmdd')) AS RATE_DAY_ID,
        CURRRENCY_RATE_ID,
        FROM_CURRRENCY_ID,
        TO_CURRRENCY_ID,
        RATE,
        VALIDITY_DATE,
        CREATE_DATE,
        -- 同币种对、同一天创建的多条汇率取ID最大的最新记录
        RANK() OVER(
            PARTITION BY TO_NUMBER(TO_CHAR(CREATE_DATE, 'yyyymmdd')), FROM_CURRRENCY_ID, TO_CURRRENCY_ID
            ORDER BY CURRRENCY_RATE_ID DESC
        ) AS RNK
    FROM CURRRENCY_RATES
),
MATCHED_RATES AS (
    SELECT 
        d.DAY_ID,
        r.CURRRENCY_RATE_ID,
        r.FROM_CURRRENCY_ID,
        r.TO_CURRRENCY_ID,
        r.RATE,
        r.VALIDITY_DATE,
        r.CREATE_DATE,
        -- 对每个日期、每个币种对，按汇率创建日期倒序取最接近的1条
        ROW_NUMBER() OVER(
            PARTITION BY d.DAY_ID, r.FROM_CURRRENCY_ID, r.TO_CURRRENCY_ID
            ORDER BY r.RATE_DAY_ID DESC, r.CURRRENCY_RATE_ID DESC
        ) AS MATCH_RNK
    FROM DAYS d
    LEFT JOIN RATE_WITH_DAYID r
        ON r.RATE_DAY_ID <= d.DAY_ID
        AND r.RNK = 1
)
SELECT 
    DAY_ID,
    CURRRENCY_RATE_ID,
    FROM_CURRRENCY_ID,
    TO_CURRRENCY_ID,
    RATE,
    VALIDITY_DATE,
    CREATE_DATE
FROM MATCHED_RATES
WHERE MATCH_RNK = 1;

方案说明

逻辑与原SQL完全一致：先筛出每个汇率创建日的最新有效记录，再为每个自然日匹配距离最近的历史汇率，当日有记录时优先返回当日记录
移除了不兼容的OUTER APPLY和FETCH NEXT语法，符合Oracle 19c物化视图的语法支持规则
集合关联+窗口函数的写法相比逐行APPLY的子查询写法，物化视图刷新时的执行效率更高，数据量较大时性能优势明显
若CURRRENCY_RATES表数据量较大，可在CREATE_DATE、FROM_CURRRENCY_ID、TO_CURRRENCY_ID列上创建联合索引，进一步提升刷新速度

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