Oracle 11g迁移至21c时ORA-00979报错差异原因咨询

问题场景 #

将Oracle 11.1迁移至21c过程中，某SQL在11g环境可正常执行，在21c环境触发ORA-00979错误。

报错SQL示例 #

select
     count(*)
from
     (
         select
             col_3
         from
             test_table
         where
             col_2 = '0'
         group by
             col_1
     );

修复后的SQL #

将子查询SELECT列表中的col_3改为col_1即可解决问题，修复后SQL如下：

select
     count(*)
from
     (
         select
             col_1
         from
             test_table
         where
             col_2 = '0'
         group by
             col_1
     );

执行计划差异 #

• Oracle 21c：直接抛出ORA-00979错误，无法生成执行计划
• Oracle 11.1执行计划：

Plan hash value: 129683286

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |       |     689  (1)| 00:00:09 |
|   1 |  SORT AGGREGATE      |            |     1 |       |            |          |
|   2 |   VIEW               | VM_NWVW_0  |     992 |       |     689  (1)| 00:00:09 |
|   3 |    HASH GROUP BY     |            |     992 |    8928 |     689  (1)| 00:00:09 |
|*  4 |     TABLE ACCESS FULL| TEST_TABLE |     27591 |    242K |     686   (1)| 00:00:09 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

    1 - SEL$DD8D4BD4
    2 - SEL$3DD9CB74 / VM_NWVW_0@SEL$DD8D4BD4
    3 - SEL$3DD9CB74
    4 - SEL$3DD9CB74 / TEST_TABLE@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

    4 - filter("COL_2"='0')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

    1 - (#keys=0) COUNT(*)[22]
    3 - (#keys=1) "COL_1"[VARCHAR2,10]
    4 - "COL_1"[VARCHAR2,10]

原因分析 #

1. SQL标准合规性要求：根据SQL标准，GROUP BY子句指定的分组列是唯一可直接出现在SELECT列表中的非聚合列，其他列必须通过聚合函数（如MAX、MIN）包裹。原SQL中col_3既不在GROUP BY列表中，也未使用聚合函数，本质上违反了语法规则。
2. Oracle 11g的宽松优化：11.1版本中，优化器检测到外层查询仅统计子查询的行数（count(*)），会忽略子查询SELECT列表中无效的col_3，直接执行GROUP BY后统计分组数量，相当于做了隐式语法兼容优化，跳过了严格校验。
3. Oracle 21c的严格校验：新版本Oracle强化了SQL语法合规性检查逻辑，无论外层查询如何使用子查询，都会先对所有嵌套查询做完整的语法合法性校验，因此直接抛出ORA-00979错误，拒绝执行不合规的SQL。

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