title: Oracle SQL WITH clause correct use case and performance
body: <p>I have to perform relatively complicated query involving multiple deep joins and complex predicates, where the result (and criteria) depends of suitable entries available meeting the criteria. There are kinda primary and secondary criteria involved, primary is applied always, and secondary strikes in if the the result is not satisfying. In a nutshell</p>
  
  <ul>
  <li>Return N (pseudo) random  distinct customers and their newest document</li>
  <li>The result should contain equal amount of documents of two different type</li>
  </ul>
  
  <p>But if there's not enough different document types or different customers with a document, try to still satisfy the amount of N documents </p>
  
  <ul>
  <li>Compensate the lack of other document type by choosing more another</li>
  <li>Compensate the lack of overall document count by using multiple documents of the same customers, when necessary</li>
  </ul>
  
  <p>I choose declarative (query) approach over imperative (cursors and counters). And this is where the <code>WITH</code> clause comes in. Roughly, by using multiple <code>WITH</code> blocks (CTE's) I declare queries (I like to think them as ad-hoc views) to declare two distinct target set for both document types. In the end, I <code>UNION</code> subsets of different CTE's for the final result, performing some <code>COUNT</code> checks to limit the amount.</p>
  
  <p>Multiple CTE's are referencing each other and are referenced from multiple places in the context of <code>COUNT</code> and <code>NOT EXISTS</code> for example. I'm novice with SQL and happened to encounter <code>WITH</code> by a coincidence, and chose to use it intuitively. Is this the correct use case for <code>WITH</code>, or an anti pattern? How does this solution compares in performance to implementing the same functionality with cursors and counters in an imperative style? Have I chosen the wrong approach? We are talking about tables with million entries.</p>
  
  <p>Here is the the query as a whole. Pardon me, I had to obscure the fields due confidentiality.</p>
  
  <pre><code>    WITH target_documents AS (
          SELECT
              &lt;Necessary fields&gt;
          FROM documents l
          WHERE
              &lt;Suitable document criteria&gt;
      ),
      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
              &lt;Necessary fields&gt;
          FROM customers a
          WHERE
              &lt;Suitable customer criteria&gt;
              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 &lt;= (&lt;N&gt; / 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 &lt;= &lt;N&gt; 
      ),
      -- 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
              &lt;Necessary fields&gt;
          FROM target_customers_type_1 a -- rows 0--(&lt;N&gt;/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
              &lt;Necessary fields&gt;
          FROM target_customers_type_2 a -- rows 0--&lt;N&gt; 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 &lt;= &lt;N&gt; - (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
          &lt;Necessary fields&gt;
      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 &lt;= &lt;N&gt; - (SELECT COUNT(1) FROM different_customers_set);
  </code></pre>
  
  <p>Is this the correct usage of <code>WITH</code> clause? Are there some obvious performance issues, where I should refactor? Or should I have done all this imperatively? Also, this query itself defines a cursor, which is used opened repeatedly in a loop (the loop defines certain criteria for customers).</p>
  
  <p>I'm especially concerned, how the optimizer handles those <code>WITH</code> blocks. Is the most efficiently plan always used (so there's not performance penalty compared to using cursors)?</p>