We use a query like this on Oracle 11.2g (in a PL/SQL stored procedure):
WITH x AS ( SELECT /*+materialize*/ FK1ID, FK2Id, FK3Id FROM SOME_VIEW WHERE some_ID = p_some_parameter) -- an IN parameter of the stored procedure
SELECT foo, bar, baz FROM t1, t2, ...
WHERE exists (
select 1 from x where
x.FK1ID= 42
or
x.FK1ID = 34 and
x.FK2ID = t2.xyz)
AND some other conditions
This is much faster (50 times) than without the +materialize hint or if we put that (sub)query in line.
But apparently it causes extra I/O (writing the subquery result to temporary table and reading it back).
Is that true? If yes, what else to use instead then?
Regards,
David
PS: The subquery would have 10-20 rows, while the main query would return tens of thousands of rows in our test case.