I think the main difference is:
The common table expression lasts only for the life of the SQL
statements
The Global temporary table (GTT) lasts for the life of the database
connection and it needs the existance of a user temporary tablespace.
May be this tablespace and the bufferpool a responsible for your better
query performance with GTT.
cheers
Florian
fb****@fastmail.fm schrieb:
Veeru71,
you might want to search for 'common table expression' which is the
more common term for 'WITH clause'
As far as I know common table expressions create temp tables like sorts
or joins sometimes do. May be some of the experts can clearify this.
cheers
Florian
Veeru71 schrieb:
Can someone point me to good documentation on 'WITH clause" ? (I
couldn't get much
out of Queries section from SQL Reference manual).
We are getting better performance when we explicity use global temp
tables to store
intermediate results than using "WITH cluase" in our queries.
Where does DB2 store the intermediate results if the query uses "WITH
clause" ?
Thanks