WITH SQL statement construct

Greens8301
Greens8301 used Ask the Experts™
on
DB 11.2

I like to know information about WITH construct of SQL statement

My understanding is that the WITH construct creates (similar to materialize or gtt) and createes
a temporary table of dataset in TEMP tablespace whaih is used later in the query for performance

The TEMP tablespace grows out of control > 50G

1. I like to know the concept of WITH statements
2. How to monitor them when the qry is running

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
A WITH clause may or may not materialize.

It's also possible it will simply be rolled into the query as an inline view.

The "MATERIALIZE" hint can sometimes force materialization, but isn't guaranteed, it's not documented either, but fairly commonly discussed.

I'm not sure what you are looking for by "concept."

They should be used as logical groupings of datasets, not as "tuning" techniques or memory structures.

Most Valuable Expert 2011
Top Expert 2012
Commented:
as for monitoring them,  IF they do create a materialization then you can look in v$sort_usage for the TEMP space consumption.

If they are instead rolled into the query as inline views, there is nothing in particular to monitor but you can see it by looking at the execution plans for a query

Author

Commented:
Can you give me a simple example of WITH as materialization and WITJ as inline view
If you can send a link for documentation, it is fine too
Most Valuable Expert 2011
Top Expert 2012

Commented:
since you can't guarantee a WITH clause will be materialized,  maybe, maybe not


but, try these...

with example as (select * from all_objects)
select * from example;


with example as (select /*+ MATERIALIZE */ * from all_objects)
select * from example;

Most Valuable Expert 2011
Top Expert 2012

Commented:
the WITH is formally called  "subquery factoring"


more info can be found here...

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2077142

Author

Commented:
In your example, if you do not put /* Materialize */ hint, iwhere does it hold the resukt set, in TEMP ?
or PGA

Thanks
Most Valuable Expert 2011
Top Expert 2012

Commented:
nowhere, it's not materialized at all, the WITH is rolled into the query
the same as if I wrote it like this...


select * from (select * from all_objects)

Author

Commented:
excellentr

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial