sql - TABLE/CAST/MULTISET 与 FROM 子句中的子查询

Question

以下查询无效。它预计会失败，因为 temp.col 引用了在该上下文中不可用的内容。

with temp as  (
       select 'A' col from dual
       union all
       select 'B' col from dual
     )
select *
from temp,
     (select level || temp.col from dual connect by level < 3);

来自 Oracle 的错误消息是:ORA-00904: "TEMP"."COL": invalid identifier

但为什么下一个查询有效？我将 CAST/MULTISET 视为一种从 SQL 表到集合类型和 TABLE 返回到 SQL 表的方法。为什么我们使用这样的往返？我想使查询工作，但如何？

with temp as  (
       select 'A' col from dual
       union all
       select 'B' col from dual
     )
select *
from temp,
     table(
       cast(
         multiset(
           select level || temp.col from dual connect by level < 3
         ) as sys.odcivarchar2list
       )
     ) t;

结果是:

COL COLUMN_VALUE
--- ------------
A   1A          
A   2A          
B   1B          
B   2B          

查看第二列如何命名为 COLUMN_VALUE。看起来像是 CAST/MULTISET 或 TABLE 构造之一生成的名称。

编辑

根据下面接受的答案，我查看了文档，发现TABLE机制是一个表集合表达式。圆括号内的表达式是集合表达式。文档定义了一种称为左相关的机制:

The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.

所以这就像 12c 中的 LATERAL。

Answer 1

Oracle 允许 lateral内联 View 以引用内联 View 内的其他表。

在旧版本中，此功能主要用于优化，如 Oracle 优化器博客中所述here .在 12c 中添加了显式横向连接。您的第一个查询只需稍作更改即可在 12c 中运行:

with temp as  (
       select 'A' col from dual
       union all
       select 'B' col from dual
     )
select *
from temp,
     lateral(select level || temp.col from dual connect by level < 3);

显然 Oracle 也默默地使用横向连接来取消嵌套集合。有少数情况下SQL使用了逻辑交叉连接，但表之间显然是密切相关的；例如 XMLTable、JSON_table 和第二个示例中的查询。在这些情况下，一起执行两个表是有意义的。我假设那里使用了横向机制，尽管执行计划和 10053 优化器跟踪都没有使用“横向”一词。该文档甚至在 Collection Unnesting: Examples 中有一个与您的示例非常相似的示例.但是，此“功能”仍未得到很好的记录。

---

附带说明一下，通常您应该避免增加上下文的 SQL 功能。横向连接、通用表表达式和相关子查询等功能可能很有用，但它们也会使 SQL 语句更难理解。常规内联 View 可以自行运行和理解，并且具有非常简单的界面 - 它的投影列。这种简单性使得将小组件组装成大语句变得更加容易。

我建议您像下面这样重新编写您的查询。像对待函数或过程一样对待每个内联 View ——给它们起好名字和注释。稍后当您将它们组合成大的、现实的陈述时，它会帮助您。

select col, the_level||col
from
(
    --Good comment 1.
    select 'A' col from dual union all
    select 'B' col from dual
) good_name_1
cross join
(
    --Good comment 2.
    select level the_level
    from dual
    connect by level < 3
) good_name_2