Skip to Main Content


what are lacks of using /*+ MATERIALIZE */ hint?

marcoDec 19 2011 — edited Dec 19 2011
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?

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
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 16 2012
Added on Dec 19 2011