ALI -- Thanks for the question regarding "WITH Clause vs global temporary tables", version 9.2
Submitted on 1-Nov-2007 14:35 Central time zone
Last updated 26-Sep-2011 19:03
You Asked
Hi Tom,
Can you tell me why is the WITH clause faster than using GTT tables in this situation?
---- WITH clause
with with_sere as (select /*+ parallel (dein,8) full(dein) */
dein.inte_no
from intr_tbl_detail_intr dein
where sysdate between dein.dein_dt_vig and nvl ( dein.dein_dt_expir, sysdate
)
and dein.dein_ind_actif = 'O'
and dein.vaat_id_val+0 = 2123380 -- sérénité oui
and dein.atgl_id+0 = 7193 -- atglid sérénité
),
with_civi as (select /*+ parallel (dein,8) full(dein) */
dein.inte_no
from intr_tbl_detail_intr dein
where sysdate between dein.dein_dt_vig and nvl ( dein.dein_dt_expir, sysdate
)
and dein.dein_ind_actif = 'O'
and dein.dein_val||'' = 'A'
and dein.atgl_id+0 = 119 -- statut civil
),
with_vigu as (select /*+ ordered parallel(peco,8) full(peco)*/
distinct poas.inte_no
from sous_tbl_pol_ass poas ,
sous_tbl_perd_couvrt peco
where sysdate between peco.peco_dt_vig and peco.peco_dt_expir
and peco.vaat_id_type_statut_dnm_stso+0 in (9, 199)
and peco.poas_id = poas.poas_id
and poas.vaat_id_lig_aff+0 = 3
)
select /*+ ordered parallel(vigu,8) full(vigu)
*/
distinct inte.inte_no
bulk collect into inte_tab
from with_vigu vigu,
intr_tbl_intr inte
where vigu.inte_no = inte.inte_no
and not exists (select 1
from with_sere
where inte_no = inte.inte_no
)
and not exists(select 1
from with_civi
where inte_no = inte.inte_no
)
and instr( upper ( inte.inte_nom_dnm || inte.inte_prenm_dnm ), 'SUCC' ) = 0
and inte.inte_type = 'I';
-- GTT tables
insert into gtt_sere (select /*+ parallel (dein,8) full(dein) */
inte_no
from intr_tbl_detail_intr dein
where sysdate between dein.dein_dt_vig and nvl ( dein.dein_dt_expir, sysdate
)
and dein.dein_ind_actif = 'O'
and dein.vaat_id_val+0 = 2123380 -- sérénité oui
and dein.atgl_id+0 = 7193 -- atglid sérénité
);
insert into gtt_civi (select /*+ parallel (dein,8) full(dein) */
inte_no
from intr_tbl_detail_intr dein
where sysdate between dein.dein_dt_vig and nvl ( dein.dein_dt_expir, sysdate
)
and dein.dein_ind_actif = 'O'
and dein.dein_val||'' = 'A'
and dein.atgl_id+0 = 119 -- statut civil
);
insert into gtt_vigu (select /*+ ordered parallel(peco,8) full(peco)*/
distinct poas.inte_no
from sous_tbl_pol_ass poas ,
sous_tbl_perd_couvrt peco
where sysdate between peco.peco_dt_vig and peco.peco_dt_expir
and peco.vaat_id_type_statut_dnm_stso+0 in (9, 199)
and peco.poas_id = poas.poas_id
and poas.vaat_id_lig_aff+0 = 3
);
select /*+ dynamic_sampling(gtt_vigu 1)
ordered full(gtt_vigu) parallel (gtt_vigu, 8)
*/
distinct inte.inte_no
bulk collect into inte_tab
from gtt_vigu,
intr_tbl_intr inte
where gtt_vigu.inte_no = inte.inte_no
and not exists (select /*+ dynamic_sampling(gtt_sere 1)
*/
1
from gtt_sere
where inte_no = inte.inte_no
)
and not exists (select /*+ dynamic_sampling(gtt_civi 1)
*/
1
from gtt_civi
where inte_no = inte.inte_no
)
and instr( upper ( inte.inte_nom_dnm || inte.inte_prenm_dnm ), 'SUCC' ) = 0
and inte.inte_type = 'I';
Regards
and we said...
you would want to compare plans. But one major difference is the optimizer is free to merge the WITH queries into the larger query - whereas with the global temporary tables - you have negated that ability altogether.
WITH does not *force* the optimizer to materialize, it simply permits it.
consider:
ops$tkyte%ORA10GR2> create table t1 as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA10GR2> create table t2 as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows=>10000000,
numblks=> 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows=>10000000,
numblks=> 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> with data
2 as
3 (select * from t1 where object_id between 1 and 100000 )
4 select *
5 from t2
6 where not exists (select 1 from data where data.object_id = t2.object_id)
7 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1142061071
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9974K| 1074M| 618K (2)| 00:48:59 |
|* 1 | HASH JOIN RIGHT ANTI| | 9974K| 1074M| 618K (2)| 00:48:59 |
|* 2 | TABLE ACCESS FULL | T1 | 25000 | 317K| 308K (2)| 00:24:28 |
| 3 | TABLE ACCESS FULL | T2 | 10M| 953M| 309K (2)| 00:24:30 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("OBJECT_ID">=1 AND "OBJECT_ID"<=100000)
ops$tkyte%ORA10GR2> with data
2 as
3 (select /*+ MATERIALIZE */ * from t2 where object_id between 1 and 100000 )
4 select *
5 from t2
6 where not exists (select 1 from data where data.object_id = t2.object_id)
7 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2779716391
------------------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
------------------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 9200K| 991M| 618K
(2)| 00:48:59 |
| 1 | TEMP TABLE TRANSFORMATION | | | |
| |
| 2 | LOAD AS SELECT | T2 | | |
| |
|* 3 | TABLE ACCESS FULL | T2 | 25000 | 2441K| 308K
(2)| 00:24:28 |
|* 4 | HASH JOIN RIGHT ANTI | | 9200K| 991M| 309K
(2)| 00:24:32 |
| 5 | VIEW | | 25000 | 317K| 110
(4)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6620_1BF817E | 25000 | 2441K| 110
(4)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T2 | 10M| 953M| 309K
(2)| 00:24:30 |
------------------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_ID">=1 AND "OBJECT_ID"<=100000)
4 - access("DATA"."OBJECT_ID"="T2"."OBJECT_ID")
ops$tkyte%ORA10GR2> set autotrace off
the first iteration of that query without the hint will almost certainly work better. Rather than full scan T1, load a temporary table, then anti-join to temporary table - we skip the load altogether
your global temporary tables, FORCE a load, PREVENT the optimizer from optimizing.
I like with :)
Get WITH it !
November 2, 2007 - 10am Central time zone
Reviewer: Duke Ganote from Amelia, Ohio USA
The WITH clause, also known as "factored subquery" or "common table expression", is useful for
modularizing SQL code. For debugging/development purposes it's sometimes useful to store subquery
results in GTTs; however, that's not a best practice for production code.
awesome..
November 2, 2007 - 11am Central time zone
Reviewer: Jay from Herndon, VA
I like 'WITH' too :-)
Makes my life so much more easier!
Thanks Tom..
Sincerely... your biggest fan.. this is stan!
(Umm.. Jay actually.. :-))
Thanks a lot and have a great weekend!
November 2, 2007 - 1pm Central time zone
Reviewer: ALI KHEMLICHE from Canada
November 5, 2007 - 8am Central time zone
Reviewer: ALI KHEMLICHE
Tanks very much Tom, your response is very helpful.
WITH-obstacles
November 7, 2007 - 4am Central time zone
Reviewer: Marc Blum from Aachen, Germany
Hi,
we are pretty much using WITH-clauses and GTTs. We found out:
- there are some bugs, when the optimizer merges the WITH-query into the main statement. Predicates
of the WITH-query are sometimes(!) not considered correctly and you get different results. We
verified this by forcing the materialization of the WITH-query by some dummy-grouping.
- when the optimizer decides to materialize the WITH-query, you get an temporary resultset, which
is not indexable. With growing resultsets, this may lead to performance getting worse. GTTs can
easily be indexed, thus supporting bigger intermediate resultsets.
Our advice is to check thoroughly how big the expected resultset of the WITH-query may be and to do
some comparisons against indexed GTTs.
With cluase vs Global Temporary tables
September 21, 2008 - 8pm Central time zone
Reviewer: Ganga from Australia
Tom,
Thank you for this website as this is really very useful for the oracle community.
I have used gtt before and was very quick for large set of rows with index created and with table
stats inserted with DBMS_STATS.set_table_stats statement.
But by using with query can I get the same performance as gtt as I can't use index or table stats.
Thanks,
Ganga Followup September 22, 2008 - 5pm Central time zone:
the with subquery is very good if you need the "subquery" for a single query - only if you need to reuse the result set, the subquery - over multiple queries would I consider a global temporary tables.
It can hash the results into memory - no need for an index if it wanted to quickly access a row by key in there.
Read consistency
September 23, 2008 - 6am Central time zone
Reviewer: Mihail Bratu from Romania
Hello,
The main difference between with clause and GTT is the read consistency. In case of with clause we
have a unique point in time of consistency, the moment when the query began.
In case of usage of GTT we have at least two moments of consistency (two SCNs), depending of the
number of GTTs.
Regards
WITH clause performance
October 13, 2008 - 2am Central time zone
Reviewer: Joel
Hi Tom,
I am trying to know if it's better to use WITH clause if i had to refer a look-up table twice in my
simple query.
for example, i have the following:
1. create table t as select object_id object_id_A,object_id object_id_b from dba_objects;
2. create table tb_objects as select object_id,object_name from dba_objects;
So querying table t, i need to use tb_objects table twice to get object_name for object_id_A and
object_id_b.
I read that using WITH clause the block will be just read once and used twice, so i thought it will
be better to use WITH clause.
These are the query i want to compare
with vw_objects as
(select object_id,object_name from tb_objects)
select t.object_id_a,oa.object_name object_name_a,t.object_id_b,ob.object_name object_name_b
from t, vw_objects oa,vw_objects ob
where t.object_id_a = oa.object_id
and t.object_id_b = ob.object_id
/
select t.object_id_a,oa.object_name object_name_a,t.object_id_b,ob.object_name object_name_b
from t, tb_objects oa,tb_objects ob
where t.object_id_a = oa.object_id
and t.object_id_b = ob.object_id
/
I analyzed the tables before comparing the execution plan and statistic. They both have the same
plan and stats.
Can you kindly enlighten me about this?
Is my understanding about WITH clause wrong?
Thank you Tom!
Followup October 14, 2008 - 4pm Central time zone:
The optimizer is free to take your with subquery and
a) just merge it - turn it into a three table join, this is what it sounds like it did. Relatively small sets of data here, it did what it wanted to do - which was just merge everything into a single query and execute it.
b) materialize your with subquery into temp and then use this result. Perhaps if your lookup table had a predicate or some complex calculation that could be avoided - it would have done it - but in this case it said "this is simple enough, there is nothing to be gained by materializing this subquery - we'll just merge the query"
RE: WITH Clause performance
October 16, 2008 - 3am Central time zone
Reviewer: Joel
So could I say if there's no complex computation nor predicate in my sub query, doing it using WITH
clause would performing the same as using the actual table, which in my example is tb_object table?
Thank you Tom for your answer.
With Caluse and the main query Relation
February 5, 2010 - 7am Central time zone
Reviewer: Raja from India
Hi Tom,
Is it possible to use the main query's table in the with clause as a join condition.
Thanks Followup February 8, 2010 - 7pm Central time zone:
I don't know what you mean.
You don't use tables in "join conditions". Not sure at all what you are trying to do.
WITH clause and main table relation
February 9, 2010 - 4pm Central time zone
Reviewer: Duke Ganote from Anderson Township, Hamilton County, Ohio USA
Use or abuse away. Here's an example with DUAL in the subfactor and main queries:
with sample_data as (
select dual.dummy as X
from dual join
dual z
on z.dummy = dual.dummy )
select t.* from sample_data t
join sample_data y
on y.X = t.X
;
X
-
X
May 5, 2010 - 7pm Central time zone
Reviewer: A reader
i need to use delete with 'with' clause ...
create table depts (deptno number);
insert into depts values (10);
insert into depts values (10);
insert into depts values (20);
insert into depts values (20);
insert into depts values (20);
insert into depts values (30);
insert into depts values (40);
insert into depts values (40);
commit;
----------------------------------------------------------
with depts as (select ROW_NUMBER () OVER ( PARTITION BY deptno ORDER BY deptno) as rnum from depts)
delete from depts
where rnum > 1
delete from depts
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
Followup May 6, 2010 - 2pm Central time zone:
you would use:
delete from depts
where rowid in (
select rid
from (select rowid rid,
row_number() over (partition by deptno order by deptno) rn
from depts)
where rn > 1
);
May 6, 2010 - 5pm Central time zone
Reviewer: A reader
"
you would use:
delete from depts
where rowid in (
select rid
from (select rowid rid,
row_number() over (partition by deptno order by deptno) rn
from depts)
where rn > 1
);
"
Did <order by deptno> needed ? Followup May 6, 2010 - 9pm Central time zone:
yes, it is needed, you cannot use row_number without order by. even though it is non-deterministic in this example, it still needs to be there.
May 6, 2010 - 10pm Central time zone
Reviewer: A reader
work in DB2 9.1 with out order by :)
with tmp(n)
as
(select 10 from sysibm.sysdummy1 union all
select 10 from sysibm.sysdummy1 union all
select 10 from sysibm.sysdummy1 union all
select 10 from sysibm.sysdummy1 union all
select 20 from sysibm.sysdummy1 union all
select 40 from sysibm.sysdummy1 union all
select 30 from sysibm.sysdummy1 union all
select 20 from sysibm.sysdummy1
)
select *
from
(
select n, row_number()over (partition by n) as rno from tmp
)X where rno >1
N RNO
10 2
10 3
10 4
20 2
Followup May 7, 2010 - 8am Central time zone:
next time I go to program in DB2, I'll keep that in mind???
not really sure what your point was otherwise...
re: programming in DB2
May 8, 2010 - 4am Central time zone
Reviewer: Stew Ashton from Paris, France
Usage of WITH Clause
May 20, 2010 - 7am Central time zone
Reviewer: Himanshu Datta from Gurgaon,India
Hi Tom,
I need to perform the following steps :-
1) Retrieve huge amount of data(6 Million) in either GTT or Use WITH.
2) Refer a table that will help in dividing the data held temporarily in GTT or WITH in to n number
of chunks/groups.
3) Every time I refer temporary data I need to open a file
,write those records,close the file and publish it at a location.
Currently,I have implemented this using temp tables.I open a cursor having the reference data in a
loop.I fire query on temp tables and write on file.
I wanted to know IF I can use WITH Clause to hold the data temporarily and then refer the data n
number of times.After each selection I need to write the file too.
Please suggest which approach is useful.
Regards,
Himanshu Followup May 24, 2010 - 11am Central time zone:
if you need to refer to it across more than one query, you cannot use with, with only has the data for that query.
The with clause can produce temporary data overflow?
September 26, 2011 - 3pm Central time zone
Reviewer: robbie from germany
Hi,
Our DBA noticed a large amount of temporary tablespace overflow due to some unexplained usage. What
I think the reason could be, is that some colleagues started to use extensively the with clause. Is
the with clause materializing its data into temporary tables which can be harmful to disk overflow?
If yes, how could be this prevented? Is there a 'flush' to be executed at the end of some
procedure?
Thanks. Robbie Followup September 26, 2011 - 7pm Central time zone:
The with clause can - but doesn't have to - use temp. If it overflows you pga workspace - it can go to disk.
But you don't managed this data at all - when you close the cursor, the associated temp space goes away.
I frankly don't know what a "tablespace overflow" is?
with clause
September 26, 2011 - 7pm Central time zone
Reviewer: A reader