A trick with rownum

Advisor failure troubleshooting

When we have a query with subqueries like this:

select
t1.a
from
(select a from table1 where b between 1 and 10) t1,
(select a from table2 where b between 1 and 10)  t2
where
t1.a = t2.a

and a is indexed,
oracle with high probability will perform nested loops for the tables.
But if selectivity of the filters is high, then more effective way to join the tables
is hash join. To force hash join we could add rownum to each of the subqueries
effectively materializing them:

select
t1.a
from
(select a, rownum from table1 where b between 1 and 10) t1,
(select a, rownum from table2 where b between 1 and 10)  t2
where
t1.a = t2.a