Re: Materialize hint

Well, I'm certainly not Jonathan, however, I'd guess that the (apparently undocumented) MATERIALIZE hint does just that, materializes a query's result set at once, (perhaps into a temporary table?). As the the "with generator" clause, that I can answer with a bit more authority, since it IS documented. It's refered to in the SQL reference manual as the "subquery factoring clause". Note that the word "generator" in the example is not a keyword, but the name that he chose to assign to this subquery. Note that later in the SQL, he's selecting from "generator v1, generator v2". This feature has been there since at least 9.2. (Not sure about 9.0.1.)

Hope that helps,

Merry Chirstmas/Happy Holidays,

-Mark

On Sat, 2004-12-25 at 00:05, Mladen Gogala wrote:

> 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

--
Mark J. Bobak
mark_at_bobak.net
"Science is the belief in the ignorance of experts."  --Richard P.
Feynman


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