sql - Oracle SQL WITH 子句正确的用例和性能

Question

我必须执行涉及多个深度连接和复杂谓词的相对复杂的查询，其中结果(和条件)取决于满足条件的合适条目。涉及到主要和次要标准，始终应用主要标准，如​​果结果不令人满意，则使用次要标准。简而言之

返回 N 个(伪)随机不同客户及其最新文档

结果应包含相等数量的两种不同类型的文档

但是如果没有足够的不同文件类型或不同客户的文件，尽量仍然满足N个文件的数量

通过选择更多另一个来弥补其他文档类型的不足

必要时通过使用同一客户的多个文档来弥补整体文档计数的不足

我选择声明式(查询)方法而不是命令式(游标和计数器)。这就是 WITH的地方子句进来了。粗略地说，通过使用多个 WITH块(CTE)我声明查询(我喜欢将它们视为临时 View )来为两种文档类型声明两个不同的目标集。最后，我 UNION最终结果的不同 CTE 的子集，执行一些 COUNT检查以限制金额。

多个 CTE 相互引用，并在 COUNT 的上下文中从多个位置引用。和 NOT EXISTS例如。我是SQL新手，碰巧遇到 WITH机缘巧合，直觉地选择使用它。这是 WITH 的正确用例吗？ ，还是反模式？该解决方案与以命令式风格使用游标和计数器实现相同功能的性能相比如何？我是否选择了错误的方法？我们正在谈论包含数百万个条目的表。

这是整个查询。原谅我，由于保密，我不得不隐瞒这些领域。

    WITH target_documents AS (
        SELECT
            <Necessary fields>
        FROM documents l
        WHERE
            <Suitable document criteria>
    ),
    target_documents_type_1 AS (
        SELECT * FROM target_documents WHERE type = 1
    ),
    target_documents_type_2 AS (
        SELECT * FROM target_documents WHERE type = 2
    ),
    target_customers AS (
        SELECT
            <Necessary fields>
        FROM customers a
        WHERE
            <Suitable customer criteria>
            AND
            EXISTS(
                SELECT 1 FROM target_documents l WHERE l.customer_id = a.customer_id 
            )
    ),
    target_customers_type_1 AS (
        SELECT * FROM target_customers a WHERE EXISTS(
            SELECT 1 FROM target_documents_type_1 l WHERE l.customer_id = a.customer_id 
        )
        AND ROWNUM <= (<N> / 2)
    ),
    target_customers_type_2 AS (
        SELECT * FROM target_customers a WHERE EXISTS(
            SELECT 1 FROM target_documents_type_2 l WHERE l.customer_id = a.customer_id 
        )
        AND a.customer_id NOT IN (
            SELECT customer_id FROM target_customers_type_1
        )
        AND ROWNUM <= <N> 
    ),
    -- This is the set, which meets the the primary criteria:
    -- Contains only distinct customers
    -- The amount of different document types is balanced as much as possible
    different_customers_set AS (
        SELECT
            <Necessary fields>
        FROM target_customers_type_1 a -- rows 0--(<N>/2) amount
        JOIN target_documents_type_1 l ON (l.customer_id = a.customer_id)
        WHERE
            l.create_dt = (SELECT MAX(create_dt) FROM target_documents_type_1 WHERE customer_id = l.customer_id)
        UNION ALL
        SELECT
            <Necessary fields>
        FROM target_customers_type_2 a -- rows 0--<N> amount
        JOIN target_documents_type_2 l ON (l.customer_id = a.customer_id)
        WHERE
            l.create_dt = (SELECT MAX(create_dt) FROM target_documents_type_2 WHERE customer_id = l.customer_id) AND
            ROWNUM <= <N> - (SELECT COUNT(*) FROM target_customers_type_1) -- Limit the total to max N rows
    )

    -- Final result: primary criteria result filled with the result of secondary criteria
    SELECT * FROM different_customers_set
    UNION ALL
    SELECT
        <Necessary fields>
    FROM target_customers a
    JOIN target_documents l ON (l.customer_id = a.customer_id AND l.document_id NOT IN (SELECT document_id FROM different_customers_set))
    WHERE
        ROWNUM <= <N> - (SELECT COUNT(1) FROM different_customers_set);

这是 WITH 的正确用法吗？条款？是否存在一些明显的性能问题，我应该在哪里重构？还是我应该强制执行所有这些操作？此外，该查询本身定义了一个游标，该游标在循环中重复打开(该循环为客户定义了某些条件)。

我特别关心的是，优化器如何处理这些 WITH块。是否总是使用最有效的计划(因此与使用游标相比没有性能损失)？

Answer 1

使用多个 CTE 本质上并不是坏事，我不时这样做是为了使代码更清晰。这是我确定查询是否符合我的性能标准的一般方法:

写出您认为将获取所需数据的查询版本。这似乎很明显，但请确保您实际上正在收集所有您想要的数据，没有您不想要的数据。

在运行之前运行解释计划(如果使用 Oracle SQL Developer，则为 F10)以评估成本。我这样做是为了确保我没有错过会导致我的查询在整个周末运行而不返回的关键连接或索引(根据经验；在我的环境中，在 SQL Dev 中取消查询只是一个建议和数据库通常会忽略请求)。

如果在解释计划中一切正常，请使用 Autotrace 运行查询以评估其实际性能。解释只是一个猜测，而自动跟踪要准确得多。

将执行计划与查询的多个版本进行比较。您是否正在尽一切努力提取最后一点性能？您是否看到任何应该使用索引的全表扫描？你有任何可以消除的嵌套循环吗？

我很少遇到这样的情况:“哇，如果没有 CTE，这个查询会更好”，尽管我几乎每天都认为相反。