主页 / dba / 问题 / 172521
Accepted
Kshitiz Sharma
Asked: 2017-05-03 06:07:12 +0800 CST

内联视图和 WITH 子句之间的区别？

  • 772

内联视图允许您从子查询中进行选择，就好像它是不同的表一样：

SELECT
    *
FROM /* Selecting from a query instead of table */
    (
        SELECT
            c1
        FROM
            t1
        WHERE
            c1 > 0
    ) a
WHERE
    a.c1 < 50;

我已经看到它使用不同的术语来提及：内联视图、WITH 子句、CTE 和派生表。对我来说，它们似乎是同一事物的不同供应商特定语法。

这是一个错误的假设吗？这些之间是否有任何技术/性能差异？

oracle cte

5 个回答

  1. Best Answer

    Oracle 中的内联视图（派生表）和 WITH 子句（CTE）之间存在一些重要区别。其中一些非常通用，即适用于其他 RDBMS。

    1. WITH可用于构建递归子查询，内联视图 - 不是（据我所知，所有支持 CTE 的 RDBMS 都是如此）
    2. 子句中的子查询WITH更有可能首先物理执行；在许多情况下，在WITH和内联视图之间进行选择会使优化器选择不同的执行计划（我猜它是特定于供应商的，甚至可能是特定于版本的）。
    3. 子查询WITH可以作为临时表实现（我不知道除 Oracle 以外的任何其他供应商是否支持此功能）。
    4. 可以在其他子查询和主查询中WITH多次引用子查询（大多数 RDBMS 都是如此）。
    • 11

  2. 其他答案很好地涵盖了语法差异，所以我不会深入探讨。相反，这个答案将只涵盖 Oracle 中的性能。

    Oracle 优化器可以选择将 CTE 的结果具体化到内部临时表中。它使用启发式而不是基于成本的优化来执行此操作。启发式类似于“如果 CTE 不是一个简单的表达式并且 CTE 在查询中被多次引用，则将其具体化”。有一些查询的具体化将提高性能。有些查询的具体化会显着降低性能。下面的例子有点做作，但很好地说明了这一点：

    首先创建一个主键包含从 1 到 10000 的整数的表：

    CREATE TABLE N_10000 (NUM_ID INTEGER NOT NULL, PRIMARY KEY (NUM_ID));

INSERT /*+APPEND */ INTO N_10000
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10000
ORDER BY LEVEL;

COMMIT;

    考虑以下使用两个派生表的查询：

    SELECT t1.NUM_ID
FROM 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t1
LEFT OUTER JOIN 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

    我们可以查看这个查询并快速确定它不会返回任何行。Oracle 也应该能够使用索引来确定这一点。在我的机器上，查询几乎立即完成，计划如下：

    好计划

    我不喜欢重复自己，所以让我们用 CTE 尝试相同的查询：

    WITH N_10000_CTE AS (
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

    这是计划：

    糟糕的计划

    这真是一个糟糕的计划。Oracle 不使用索引，而是将 10000 X 10000 = 100000000 行具体化到临时表中，最终返回 0 行。该计划的成本约为 6 M，远高于其他查询。在我的机器上完成查询需要 68 秒。

    请注意，如果临时表空间中没有足够的内存或可用空间，则查询可能会失败。

    我可以使用未记录的INLINE提示来禁止优化器实现 CTE：

    WITH N_10000_CTE AS (
  SELECT /*+ INLINE */ n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

    该查询能够使用索引并几乎立即完成。查询的成本与之前相同，为 11。因此，对于第二个查询，Oracle 使用的启发式算法导致它选择了一个估计成本为 6 M 的查询，而不是一个估计成本为 11 的查询。

    • 11

  3. 对于 SQL Server，WITH CTE指定临时命名结果集，但只有第一个CTE. IE

    WITH CTE AS (SELECT .... FROM), 
CTE2 AS (SELECT .... FROM)

SELECT CTE.Column, CTE2.Column
FROM CTE
INNER JOIN CTE2 on CTE.Column = CTE2.Column

    但这不是子查询或相关子查询。有些事情可以用 CTE 做而不能用 SQL Server 中的子查询做，比如更新 CTE 中引用的表。以下是使用 CTE 更新表的示例。

    子查询类似于

    SELECT
   C1,
   (SELECT C2 FROM SomeTable) as C2
FROM Table

    或者，如果您要根据 a.c1 引用/加入/限制结果，则相关子查询是您在 OP 中提供的。

    因此，它们绝对不是一回事，尽管在很多情况下，您可以使用其中一种或多种方法来获得相同的结果。这仅取决于最终结果是什么。

    • 1

  4. Oracle 中子句和子查询的主要区别在于with，您可以在子句中多次引用一个查询。然后，您可以对其进行一些优化，例如使用materialize提示将其转换为临时表。您还可以通过在with子句中引用自身来使用它进行递归查询。内联视图无法做到这一点。

    更多信息可以在这里和这里找到。

    • 1

  5. 您需要小心 SQL 服务器中的 CTE，而不仅仅是 oracle，在某些情况下，与子查询、交叉应用等相比，使用 CTE 时查询的性能要差得多。

    与往常一样，重要的是在各种负载条件下测试任何查询以确定哪个最有效。

    与使用 oracle 的 @scsimon 类似，有时 MS SQL 服务器在索引使用方面没有达到您的预期。

    如果您要多次使用相同的数据，CTE 可能更有用，如果您只使用一次，通常子查询在大型数据集中更快。

    例如 select * from (my subquery) join something else ...

    • 0

相关问题