I don 't know that I 've ever seen the MATERIALIZE hint documented, but I had
a pretty good guess as to what it *probably* does and ran a quick little
test to see. The WITH clause, also known as subquery factoring, allows you
to, among many things, define a piece of SQL that that can then be used in
the rest of your query. Sometimes for readability, but in the cases where
I 've used it, it needed to be referred to in different parts of a UNION ALL
query, and served as the driving data. And the query was pretty expensive,
so I didn 't want to execute it twice. By using the WITH clause I am able to
write it once and have the results used in each part of the query.
My experience has been that when a WITH clause query is referenced more than
once in a query, it materializes the results in a TEMP table. But I don 't
know that that is a hard and fast rule, just what I 've seen in my cases. It
looks to me, though, that the materialize hint probably forces the data in
the WITH clause to be materialized in a temporary table.
Take the following query (not that I would write it this way, just as an
example of the WITH clause):
With Foo as (select deptno from dept)
select ename from emp, foo
where emp.deptno = foo.deptno
And then do it with the MATERIALIZE hint:
With Foo as (select /*+ materialize */ deptno from dept)
select ename from emp, foo
where emp.deptno = foo.deptno
If you run a trace, you will see a typical plan for the first query, with
the WITH query being merged in and looking like a normal plan. But look at
the trace for the second query, you will see (1) the creation of a temporary
table, (2) an insert statement, based on the WITH query, inserting into the
global temporary table, and (3) the use of the global temporary table in the
query plan.
So my take on it is that the MATERIALIZE hint is used to *force* the results
of the WITH clause to be written to a global temporary table, which seems to
imply that maybe even if the WITH clause is referenced multiple times in a
query, that it doesn 't necessarily materialize the results into a global
temporary table as I normally see. So let 's hear from Jonathan -- but in the
test above the MATERIALIZE hint forced the WITH clause data to be inserted
into a temp table.
Regards,
Larry G. Elkins
elkinsl@(protected)
214.954.1781
> -- --Original Message-- --
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)]On Behalf Of Mladen Gogala
> Sent: Friday, December 24, 2004 11:05 PM
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
