Hi all,
I'm in process of testing heavy sql statement and found amazing feature for decreasing time of performing statement.
with t1 as (select /*+ MATERIALIZE */ 1 as t, 2 as y from dual),
t2 as (select /*+ MATERIALIZE */ 1 as t, 2 as y from dual)
select * from t1, t2 where t1.t = t2.t
It's hard to imagine some heavy sample statement to demonstrate here, but in few words this hint in nested sql statements allows in my real case to decrease performing time from 40 min to 1,5 min!
I'm just interesting what are lacks of using this hint?
Upd:
I found that materialize hint is undocumented, so it can be deprecated or just stop working in next version of Oracle. I can use rownum in subquery with the same effect (subquery becomes materialized).
Could you please confirm my guess?
Edited by: marco on Dec 19, 2011 3:24 AM