Re: MATERIALIZE hint & CTAS WITH statement !

"Spendius" <spendius_at_muchomail.com> wrote in message news:1126258529.717992.235460_at_z14g2000cwz.googlegroups.com... Hi,
I found a statement on J. Lewis' site about which I feel particuliarly puzzled, it starts as follows:
> create table t1 as
> with generator as (
> select --+ materialize
> rownum id,
> ...

2 questions:
1/ whatever the site/manual I delve into, I couldn't

   find any description of the MATERIALIZE hint, and 2/ what is this "CREATE TABLE x AS WITH x AS" statement ?!?

(I first thought the reserved word was 'generator', then tried with 'zorglub' and saw I had no syntax error; for this stuff I neither found absolutely NOTHING whether on Metalink, Tahiti etc.)

Can someone please enlighten me sur ce coup-là ?!? Thanks.

The general concept is known as subquery factoring, more commonly seen in queries such as:

with subquery_alias1 as (

    some bit of sql
),
subquery_alias2 as (

    some other bit of sql
)
select
from

    subquery_alias1,
    subquery_alias2,
    tableA,
    tableB,
    view1
where

    ....

Oracle has the option of creating an in-memory global temporary table for each factored subquery, or simply substituting the defining SQL whenever it sees the subquery name. Both methods will be examined and costed, and the cheaper chosen.

The materialize hint forces the creation of the global temporary table
The inline hint forces the query to be written inline.

I probably got the hint names from scanning a binary, and guessed what they did when I found out about subquery factoring, I don't remember seeing them in any of the manuals.

The create table as with ...
was just a guess I tried one day.

1. The following is legal and well-known create table X as select statement;
2. The following is legal, but less well-known: A select statement may be written as: with alias as ( select statement ) select ...
3. Inference (substituting 2 in 1) create table X as with alias as ( select statement ) select ... from alias etc...

        might work - and it did.

You can also do:

    insert into tableA
    with alias as (

        select statement
    )
    select ... from alias etc.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005