Re: Materialize hint

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 27 Dec 2004 17:41:35 -0000
Message-ID: <014001c4ec3b$4f9c5e90$6702a8c0@Primary>

In reverse order, the "with" clause names and defines a subquery before its use in a query - a bit like a macro in C.

Unlike C macros though, the optimizer can choose to write your subquery in-line and then optimise the expanded statement, or create a temporary table from the definition and use the temporary table for the main query.

You can choose to use the 'with' clause simply to make a complex SQL statement tidier, knowing that there should be no performance benefit in creating a temporary table.

If you want to control the optimiser, then the 'materialize' hint makes it create a temporary table; the 'inline' hint makes it perform 'macrosubstitution'.

As far as I know, neither hint is documented.

The 'with' clause (known as subquery factoring) is quite flexible - though not yet as flexible as DB2's which can cope with recursive definitions). Here's an example I wrote to answer a fun puzzle that Daniel Morgan put out on cdo.server some months ago.

with age_list as (
 select rownum age
 from all_objects
 where rownum <= 36
),

product_check as (
 select

  age1.age    as youngest,
  age2.age    as middle,
  age3.age    as oldest,
  age1.age + age2.age +age3.age as summed

 from

  age_list age1,
  age_list age2,
  age_list age3

 where
  age2.age >= age1.age
 and age3.age >= age2.age
 and age1.age * age2.age * age3.age = (
   select max(age) from age_list
  )
),

summed_check as (
select
 youngest, middle, oldest, summed
from
 (
 select
  youngest, middle, oldest, summed,
  count(*) over(partition by summed) ct
 from product_check
 )
where ct > 1
)
select
 *
from summed_check
where
 oldest > middle
;

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

• Original Message ----- From: "Mladen Gogala" <gogala_at_sbcglobal.net> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Saturday, December 25, 2004 5:05 AM 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.

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

Received on Mon Dec 27 2004 - 13:23:36 CST