Most subqueries will perform better after being dropped and re-created.
However, an expression subquery containing an aggregate where the outer table is very large and has few duplicate correlation values, and the inner table is small, may not perform as well in release 11.0. For example:
select * from huge_table where x=
(select sum(a) from tiny_table
where b = huge_table.y)
The workaround is:
select huge_table.y, s=sum(a)
into #t
from huge_table, tiny_table
where b=huge_table.y
group by huge_table.y
select huge_table.*
from huge_table, #t
where x=#t.x
and huge_table.y=#t.y
