Materialize hint

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 25 Dec 2004 05:05:08 +0000
Message-Id: <1103951108l.29327l.0l@medo.noip.com>

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

begin
 dbms_stats.gather_table_stats(user, 't1', cascade =3D> true); end;
.
/

select
 placed
from (
 select id, rank() over (order by id) placed  from t1
 )
where
 placed =3D id - 1
and rownum =3D 1
;

--=20
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

---

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 24 2004 - 23:00:31 CST