sql - Oracle SQL 查询在 12C 中工作但在 11g 中无效

Question

我有一个 Oracle SQL 查询，它在 12C 中运行良好，但在 11g 中不能正常运行。我在下面给出了一个类似的例子。请解释这是否是 12C 中修复的错误/增强功能。

CREATE TABLE MSI_OWNER.VINOTH_TEST1
(
  COL1  VARCHAR2(100 BYTE),
  SAL   NUMBER,
  YEAR  NUMBER
)

Insert into MSI_OWNER.VINOTH_TEST1   (COL1, SAL, YEAR) Values   ('Vinoth', 100, 1);
Insert into MSI_OWNER.VINOTH_TEST1   (COL1, SAL, YEAR) Values   ('Vinoth', 100, 2);
COMMIT;

  SELECT col1,
         (SELECT MAX (its)
            FROM (SELECT MAX (year) its
                    FROM vinoth_test1 x
                   WHERE x.col1 = a.col1))
            max_year,
         sal
    FROM vinoth_test1 a
GROUP BY col1, sal

请注意，我已经重新编写了一个不同的逻辑来解决这个问题，但我想知道这是 11g 中的错误还是 12C 中的增强。

Error in 11g: ORA-00904: "A"."COL1": invalid identifier

Answer 1

在任一数据库中，您都可以将其写为:

select col1, sal,
       max(max(year)) over (partition by col1)
from vinoth_test1
group by col1, sal;

不需要子查询。正如所指出的，您不需要附加级别的子查询。无论如何，最里面的子查询只返回一行。

Oracle 只允许对子查询中的直接父级的相关引用——而不是更高级别的父级。这似乎认为您的查询在任何版本的 Oracle 中都不起作用。但是，我相信 Oracle 12c 在强加这条规则之前做了一些优化。 documentation暗指这个:

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. . . . A correlated subquery conceptually is evaluated once for each row processed by the parent statement. However, the optimizer may choose to rewrite the query as a join or use some other technique to formulate a query that is semantically equivalent. Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.

我怀疑这种优化正在删除您不必要的子查询，从而允许查询编译。