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 'macro-
substitution '.
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@(protected) >
To: "oracle-l " <oracle-l@(protected) >
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.
