Oracle SQL WITH clause correct use and performance

Question

I need to execute a relatively complex query involving multiple deep connections and complex predicates where the result (and criteria) depends on the matching records available to match the criteria. There are related primary and secondary criteria, primaryity is always applied, and secondary impacts, if the result does not satisfy. In a nutshell

• Returns N (pseudo) random individual customers and their latest document.
• The result should contain the same number of documents of two different types.

But if you do not have enough different types of documents or different clients with a document, try to satisfy the number of N documents

• Confirm the absence of a different type of document by selecting another
• Compensate for the lack of the total number of documents, using, if necessary, several documents of the same clients

I choose the declarative (query) approach on the imperative (cursors and counters). And here comes the offer WITH. For example, using multiple blocks WITH(CTEs), I declare requests (I like to consider them ad-hoc representations) to declare two different target sets for both types of documents. In the end, I have UNIONsubsets of different CTEs for the final result, performing some COUNTchecks to limit the amount.

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 , , . , :

• , , , . , , , , - , .

• (F10 Oracle SQL Developer), . , , , -, ( ; SQL Dev - ).

• , Autotrace, . - , Autotrace .

• . , ? , ? - , ?

- , : ", , CTE", , .

Answer 2

• , CTE. - .
• target_customers_type_2 CTE (, target_documents_type_2).
• ROWNUM "" , . .
• ", ", . ?

Answer 3

- , /. .

, : -)

SQL, , , PL/SQL, , , .

WITH , . , : , , , .

, . WITH , ()