用于with子句多次访问的表。

Question

我对Oracle " with“子句的理解是，如果我们想在查询中多次使用表，那么将它保留在with子句中，并在查询的其余部分使用with子句名，以避免在相同的表上多次命中。参见下面的代码，为什么双表要被访问两次？

    explain plan for
    with temp as
    (
    select * from dual
    )
    select * from temp
    union all
    select * from temp;

select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
Plan hash value: 716948595
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |     4 |     4   (0)| 00:00:01 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Answer 1

其实不是这么回事。甲骨文称它为"subquery_factoring_clause"，文件上说

subquery_factoring_clause允许您为子查询块分配名称(query_name)。然后，您可以通过指定query_name来引用查询中的多个位置。Oracle通过将query_name视为内联视图或临时表来优化查询。

WITH子句为优化器提供了实现子查询或缓存子查询的选项(即只运行一次子查询)，但优化器仍将做出自己的决定。由于访问dual非常便宜(它已经在内存中)，因此在本例中它将其视为内联视图并执行视图合并，因此将查询重写为如下所示：

select * from (select * from dual)
union all
select * from (select * from dual)

如果您的子查询更复杂，Oracle将实现它。有关示例，请参阅这个关于DBA SE的答案 (以及说明有时会影响性能)。这个Oracle文档还很好地解释了内联视图与因素子查询之间的关系。

Answer 2

优化器可以自由地考虑内联视图(如您的示例中所做的)或将其物化。如果希望强制优化器物化，请使用materialize提示；但如果看不到改进，则删除它。有时候优化器是错误的，但有时你是错的。

您可能想知道为什么优化器不选择在示例中实现视图。您可能会认为，在视图内联的情况下，运行时将访问表两次，而且您可能认为这会产生过多的硬盘访问。这可能是错误的(这可能是优化器的观点)：它将访问数据两次，但它很可能缓存它；只有一个访问将来自磁盘(缓慢)，而第二个访问将来自缓存。如果视图是物化的，那么它必须写在某个地方；即使是缓存，您也必须读取磁盘一次(计算视图)，然后从视图(缓存)读取数据两次。工作比内衬多。