oracle - 带子句执行

Question

我一直认为With子句作为一次性执行语句工作，它的行为与普通表一样 - 您可以像在常规表上一样对其执行所有 SQL 操作。

但事实证明，在几个数据库(Oracle、Netezza、Sybase、Teradata)中 with每次使用时都会执行子句。

 With Test as(

    select random() --pseudo code

)
select '1st select', * from Test
union
select '2nd select', * form Test

上面的查询不是 2 个相同的数字，而是返回 2 个不同的数字，因此它为每个选择执行。

如果我在 With 中有一个非常复杂的查询子句，我在查询的其余部分使用它 5 次，它会执行 5 次，这对我来说似乎非常无效。

那么有人可以给我一个很好的合乎逻辑的理由它是这样工作的吗？

Answer 1

在 oracle world ，如解释 here , WITH query_name 子句允许您为子查询块分配名称。然后，您可以通过指定查询名称在查询中的多个位置引用子查询块。 Oracle 通过将查询名称视为内联 View 或临时表来优化查询。

您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。查询名称对主查询和所有后续子查询可见，但定义查询名称本身的子查询除外。

当 WITH 查询的结果在主查询的主体中需要超过一次时，WITH 子句最有值(value)，例如需要将一个平均值与两到三次进行比较。关键是尽量减少对多次连接到单个查询中的表的访问次数。

子查询因式分解的限制:

您不能嵌套此子句。也就是说，您不能在另一个 subquery_factoring_clause 的子查询中指定 subquery_factoring_clause。但是，在一个 subquery_factoring_clause 中定义的 query_name 可以在任何后续 subquery_factoring_clause 的子查询中使用。

在带有集合运算符的查询中，集合运算符子查询不能包含 subquery_factoring_clause，但 FROM 子查询可以包含 subquery_factoring_clause。

在您的情况下，您使用了一个随机函数，优化器将对其进行不同的处理，将其视为内联 View 而不是物化 View 。由于@ ibre5041 建议使用 EXPLAIN PLAN对于不同的情况。

考虑递归 CTE 的情况，它每次都在内部使用。

WITH generator ( value ) AS (
  SELECT 1 FROM DUAL
UNION ALL
  SELECT value + 1
  FROM   generator
  WHERE  value < 10
)
SELECT value
FROM   generator;

Plan hash value: 1492144221

--------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |      |     2 |    26 |     4   (0)| 00:00:01 |
|   1 |  VIEW                                     |      |     2 |    26 |     4   (0)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|      |       |       |            |          |
|   3 |    FAST DUAL                              |      |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    RECURSIVE WITH PUMP                    |      |       |       |            |          |
--------------------------------------------------------------------------------------------------

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

   4 - filter("VALUE"<10)