Avoid Nested Queries

Performance impact: Variable

If you can, it is recommended that you should rewrite your query to avoid nested queries that need materialization of many rows.

The following are examples of nested queries that may need to be materialized and result in multiple rows:

  • Aggregate nested query with GROUP BY

  • Nested queries that reference ROWNUM

  • Union, intersect, or minus nested queries

  • Nested queries with ORDER BY

For example, the following aggregate nested query results in an expensive performance impact:

Command> SELECT * FROM (SELECT SUM(x1) sum1 FROM t1 GROUP BY y1), 
(SELECT sum(x2) sum2 FROM t2 GROUP BY y2) WHERE sum1=sum2;

The following is an example of a nested query that references ROWNUM: 

Command> SELECT * FROM (SELECT rownum rc, x1 FROM t1 WHERE x1>100), 
(SELECT ROWNUM rc, x2 FROM t2 WHERE x2>100) WHERE x1=x2;

The following is an example of a union nested query:

Command> SELECT * FROM (SELECT x1 FROM t1 UNION SELECT x2 FROM t2), 
(SELECT x3 FROM t3 GROUP BY x3) WHERE x1=x3;

See Subqueries in the Oracle TimesTen In-Memory Database SQL Reference.