Get WITH it !
Duke Ganote, November 02, 2007 - 10:31 am UTC
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..
Jay, November 02, 2007 - 11:43 am UTC
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!
ALI KHEMLICHE, November 02, 2007 - 1:38 pm UTC
ALI KHEMLICHE, November 05, 2007 - 8:33 am UTC
Tanks very much Tom, your response is very helpful.
WITH-obstacles
Marc Blum, November 07, 2007 - 4:49 am UTC
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
Ganga, September 21, 2008 - 8:56 pm UTC
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
September 22, 2008 - 5:01 pm UTC
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
Mihail Bratu, September 23, 2008 - 6:03 am UTC
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
Joel, October 13, 2008 - 2:40 am UTC
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!
October 14, 2008 - 4:44 pm UTC
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
Joel, October 16, 2008 - 3:30 am UTC
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
Raja, February 05, 2010 - 7:50 am UTC
Hi Tom,
Is it possible to use the main query's table in the with clause as a join condition.
Thanks
February 08, 2010 - 7:40 pm UTC
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
Duke Ganote, February 09, 2010 - 4:02 pm UTC
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
A reader, May 05, 2010 - 7:26 pm UTC
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
May 06, 2010 - 2:20 pm UTC
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
);
A reader, May 06, 2010 - 5:26 pm UTC
"
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 ?
May 06, 2010 - 9:03 pm UTC
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.
A reader, May 06, 2010 - 10:01 pm UTC
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
May 07, 2010 - 8:18 am UTC
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
Stew Ashton, May 08, 2010 - 4:21 am UTC
DB2 9.1 must be the Unix version, which claims to support Oracle SQL extensions and Oracle PL/SQL. If DB2 is allowing something that Oracle isn't, it just means DB2 isn't copying Oracle very closely.
I can't wait to port my queries with "AS OF SCN..." to DB2 :)
Usage of WITH Clause
Himanshu Datta, May 20, 2010 - 7:52 am UTC
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
May 24, 2010 - 11:37 am UTC
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?
robbie, September 26, 2011 - 3:34 pm UTC
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
September 26, 2011 - 7:03 pm UTC
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
A reader, September 26, 2011 - 7:51 pm UTC
Bugs in WITH clause
Kit.net, March 10, 2013 - 5:27 pm UTC
I appreciate Marc Blum's mention of bugs in the WITH clause implementation; I have seen WITH blocks causing core dumps ("access violation") in udump trace files for Oracle 10g, but support is no longer accepting bugs in this version (nor offering guidance on how to avoid it) -- so this page is quite helpful overall.
One point not mentioned: GTTs cannot be used in a PL/SQL FUNCTION, so I have to redesign using a PROC in order to work around these issues.
A friend tells me WITH blocks are notorious for consuming SGA memory, and recommends GTTs instead.
March 11, 2013 - 9:36 am UTC
I use with blocks *all of the time*
they do not consume SGA memory, they use blocks from the buffer cache - just like everything else.
I recommend with blocks over global temporary tables all of the time, pretty much most every time. If and only if you need the temporary result set for more than one query would I suggest a global temporary table.
A reader, March 11, 2013 - 11:05 am UTC
"If and only if you need the temporary result set for more than one query would I suggest a global temporary table."
If i understand correctly you mean, it can be usefull to re-use temp result set when processing data in PL/SQL right?
Thanks.
March 11, 2013 - 1:39 pm UTC
if you need that temporary result for more than one query - in any language - a global temporary table can be useful.
global temp tables created - consume temp space
kit.net, March 15, 2013 - 12:04 pm UTC
Tom, thanks for your prompt response!
I would prefer to use WITH blocks *all the time* as well.
Is it possible that the 13 global temp tables created by SYS from my WITH block queries (as shown by TKPROF) consume memory (somewhere) so that I need to use a COMMIT after my [very big] SELECT to free it (and thus prevent these core dumps with [ACCESS_VIOLATION] ... [UNABLE_TO_READ])?
(We're running 10.2.0.5.0 - 64bi.)
Thanks.
March 25, 2013 - 3:02 pm UTC
you need not worry about them. When not in use, they consume 0 bytes of anything.
if you are getting access violations and ora-600's, you are hitting a bug, you should contact support, it should not happen in any circumstance
What's gives?
Al Ricafort, July 10, 2013 - 8:14 am UTC
Hi Tom,
Do you know why the first version of the pl/sql failed?
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> declare
2 v1 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('10','11','12','13');
3 v2 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('C','C','C','C');
4 v3 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('1','2','3','4');
5 begin
6 FOR cur in
7 (WITH T AS
8 ( SELECT aa.COLUMN_VALUE C1,
9 ab.COLUMN_VALUE C2,
10 ac.COLUMN_VALUE C3
11 FROM
12 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v1 as VARRTYPE_VARCHAR10))) aa,
13 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v2 as VARRTYPE_VARCHAR10))) ab,
14 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v3 as VARRTYPE_VARCHAR10))) ac
15 WHERE AA.RN = AB.RN AND AB.RN = AC.RN
16 )
17 SELECT d.C1,
18 d.C2,
19 d.C3
20 FROM
21 DUAL,
22 T d
23 UNION
24 SELECT d1.C1,
25 d1.C2,
26 d1.C3
27 FROM DUAL,
28 T d1
29 )
30 LOOP
31 dbms_output.put_line(cur.C1);
32 END LOOP;
33 end;
34
35 /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 6
SQL> declare
2 v1 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('10','11','12','13');
3 v2 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('C','C','C','C');
4 v3 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('1','2','3','4');
5 begin
6 FOR cur in
7 (WITH T AS
8 ( SELECT aa.COLUMN_VALUE C1,
9 ab.COLUMN_VALUE C2,
10 ac.COLUMN_VALUE C3
11 FROM
12 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v1 as VARRTYPE_VARCHAR10))) aa,
13 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v2 as VARRTYPE_VARCHAR10))) ab,
14 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v3 as VARRTYPE_VARCHAR10))) ac
15 WHERE AA.RN = AB.RN AND AB.RN = AC.RN
16 )
17 SELECT d.C1,
18 d.C2,
19 d.C3
20 FROM
21 DUAL,
22 T d
23 /*UNION
24 SELECT d1.C1,
25 d1.C2,
26 d1.C3
27 FROM DUAL,
28 T d1*/
29 )
30 LOOP
31 dbms_output.put_line(cur.C1);
32 END LOOP;
33 end;
34 /
PL/SQL procedure successfully completed.
SQL> declare
2 v1 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('10','11','12','13');
3 v2 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('C','C','C','C');
4 v3 VARRTYPE_VARCHAR10:=VARRTYPE_VARCHAR10('1','2','3','4');
5 begin
6 FOR cur in
7 (WITH T AS
8 ( SELECT aa.COLUMN_VALUE C1,
9 ab.COLUMN_VALUE C2,
10 ac.COLUMN_VALUE C3
11 FROM /*
12 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v1 as VARRTYPE_VARCHAR10))) aa,
13 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v2 as VARRTYPE_VARCHAR10))) ab,
14 (SELECT COLUMN_VALUE, ROWNUM RN FROM TABLE(CAST(v3 as VARRTYPE_VARCHAR10))) ac(
15 */
16 (SELECT '10' COLUMN_VALUE, ROWNUM RN FROM DUAL) aa,
17 (SELECT 'C' COLUMN_VALUE, ROWNUM RN FROM DUAL) ab,
18 (SELECT '1' COLUMN_VALUE, ROWNUM RN FROM DUAL) ac
19 WHERE AA.RN = AB.RN AND AB.RN = AC.RN
20 )
21 SELECT d.C1,
22 d.C2,
23 d.C3
24 FROM
25 DUAL,
26 T d
27 UNION
28 SELECT d1.C1,
29 d1.C2,
30 d1.C3
31 FROM DUAL,
32 T d1
33 )
34 LOOP
35 dbms_output.put_line(cur.C1);
36 END LOOP;
37 end;
38 /
PL/SQL procedure successfully completed.
July 16, 2013 - 3:19 pm UTC
bug, does not reproduce in current releases.