RE: Materialize hint
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <gogala@xxxxxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 25 Dec 2004 12:36:53 +0100
Mladen,
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!
Lex.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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);
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
