Category: English

In some cases we can avoid copying sub-query result to the temporary table. We can even use sub-query cursor “as is” – so it could be used as a result of the whole query. In other words there  is almost zero overhead against a simple query.

For now such optimization can be applied if a query:

1. contains only “*” item in the select list clause
2. has no “where” clause
3. has no “group by” clause
4. has no “having” clause
5. has no “distinct” clause
6. is not a part of some join operation
7. has no “order by” clause
8. has no binding values
9. sub-query is not a “union/except/intersect” result

Examples:

SELECT * FROM (SELECT * FROM t1 WHERE f1 > 5);

SELECT * FROM (SELECT t1.f1, t2 f1 FROM t1 JOIN t2 ON l1 WHERE t1.f1 > 5 AND t2.f1 <3);