see MetaLink:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=FOR&p_id=215086.995
hope this helps, and Happy X-mass to all of you!
-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of Mladen Gogala
Sent: Saturday, December 25, 2004 06:05
To: oracle-l
Subject: Materialize hint
I recently ran accross a SQL by Jonathan that uses "materialize " hint.
As was unable to find the hint documented anywhere, and God knows I tried
before asking the question, I must ask the folowing two =20
questions:
1) What does "materialize " hint do and where is it documented?
I was unable to find it documented in either 10g documentation
or 9.2 documentation.
2) The same question for "with generator " clause.
This question is, of course, meant for Jonathan but I 'd appreciate anybody
else 's answer as well.
Here is the SQL:
*******************************************************************
drop table t1;
create table t1
nologging
pctfree 0
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <=3D 1000
)
select
/*+ ordered use_nl(v2) */
rownum - 1 id
from
generator v1,
generator v2
where
rownum <=3D 65536
;
delete from t1 where id =3D 65001;
delete from t1 where id =3D 65535;
alter table t1 add constraint t1_pk primary key (id);
