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);
