RE: Materialize hint

Mladen,

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_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]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
>

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