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

我必须执行涉及多个深度连接和复杂谓词的相对复杂的查询,其中结果(和标准)取决于符合条件的可用条目.有一些主要和次要标准涉及,主要是始终应用??,如果结果不满意则进行二次打击.简而言之

>返回N(伪)随机不同客户及其最新文档
>结果应包含两种不同类型的等量文件

但是如果没有足够的不同文档类型或不同客户的文档,请尝试仍然满足N个文档的数量

>通过选择更多其他文档来补偿缺少其他文档类型
>必要时,通过使用同一客户的多个文档来补偿缺少总体文档数量

我选择声明式(查询)方法而不是命令式(游标和计数器).这就是WITH子句的用武之地.粗略地说,通过使用多个WITH块(CTE),我声明了查询(我喜欢将它们视为ad-hoc视图)来为两种文档类型声明两个不同的目标集.最后,我为最终结果提供了不同CTE的UNION子集,执行一些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块.总是使用最有效的计划(因此与使用游标相比,没有性能损失)？

解决方法

使用多个CTE本身并不是坏事,我不时这样做以制作更清晰的代码.这是我确定查询是否符合我的性能标准的一般方法：

>写出您认为可以获取所需数据的查询版本.这似乎是显而易见的,但请确保您实际上收集了您想要的所有数据,而没有您不想要的数据.
>运行解释计划(如果使用Oracle SQL Developer,则运行F10)以在运行之前评估成本.我这样做是为了确保我没有错过一个关键的连接或索引,这会导致我的查询在整个周末运行而不返回(从经验来讲;在我的环境中,取消SQL Dev中的查询只是一个建议,数据库通常会忽略请求).
>如果在Explain计划中一切正常,请使用Autotrace运行查询以评估其实际性能.解释只是一个猜测,而Autotrace则更准确.
>将执行计划与查询的多个版本进行比较.您是否正在尽一切努力提取最后一点性能？您是否看到应该使用索引的全表扫描？你有任何可以消除的嵌套循环吗？

我很少遇到一种情况,我认为自己“哇,如果没有CTE,这种查询会更好”,尽管我认为几乎每天都相反.