Oracle查询执行机制及改写后查询的执行逻辑咨询

Great question—let's break down how Oracle handles both versions of your query, and why your rewrite is a solid optimization choice.

How the Original Query Works #

Your original query follows this flow:

1. Execute the two inner SELECT statements (from Foo and Foo2, each excluding rows where x is in their respective subqueries).
2. Combine the results with UNION (which automatically removes duplicate rows).
3. Apply the outer WHERE filter (y IN ('a','b') AND z IN ('xa','xb','xc')) to the entire combined result set.

The problem here is that step 2 generates a potentially large intermediate dataset before filtering out unwanted rows. Even if Oracle's Cost-Based Optimizer (CBO) tries to optimize this, the nested subquery structure can sometimes prevent the optimizer from pushing the outer filter down to the inner queries automatically—especially if statistics are outdated or the subqueries (sq1, sq2) are complex.

Why Your Rewritten Query is Better #

Your rewritten query flips the order: it applies the y and z filters before the UNION:

SELECT x,y,z FROM Foo 
WHERE x NOT IN (sq1) AND y IN ('a','b') AND z IN ('xa','xb','xc')
UNION
SELECT x,y,z FROM Foo2 
WHERE x NOT IN (sq2) AND y IN ('a','b') AND z IN ('xa','xb','xc')

Here's how Oracle handles this:

• For each branch of the UNION, Oracle first filters rows from Foo/Foo2 using both the x NOT IN (...) condition and the y/z filters. This drastically reduces the number of rows that need to be processed in the UNION step.
• If Foo or Foo2 have indexes on y and z (or a composite index on y,z), Oracle can use these indexes to quickly retrieve only the rows that match the filter conditions, avoiding full table scans entirely.
• The UNION operation now works with a much smaller dataset, making the entire query faster and less resource-intensive.

Will Oracle Automatically Optimize the Original Query? #

Oracle's CBO can perform predicate pushdown (moving outer filters into inner subqueries) in some cases, but it's not guaranteed. Factors like:

• Outdated table/index statistics
• Complexity of sq1/sq2
• The presence of UNION (which adds a distinct sort step)

can prevent the optimizer from making this optimization. By rewriting the query manually, you're removing any ambiguity and ensuring the filter is applied as early as possible.

Key Note: Result Consistency #

Since UNION removes duplicates, both queries will return exactly the same results. Filtering before or after the UNION doesn't change the final output—you're just removing unwanted rows earlier in the process, which is always more efficient.

内容的提问来源于stack exchange，提问作者Renaud is Not Bill Gates