CODEWITHSUNDEEP
sqldb2common-table-expression
grape_mao
grape_mao

Reputation: 1153

DB2 With Clause

I am new to DB2 and I have a question about the with clause. For example in the following query:

WITH values AS 
   (
      SELECT user_id, user_data FROM USER WHERE user_age < 20
   )       
SELECT avg(values.user_data) FROM values
UNION
SELECT sum(values.user_data) FROM values

How many times will the common table expression be executed? Will the result of the with clause be stored in a temporary table or it will do sub-select twice. (I use with and union here just to give an example, and sorry for my poor english)

Upvotes: 3

Views: 58119

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

In DB2, common table expressions should create the Common Table Expression Node in the execution plan (see the documentation here). This node explicitly says:

They serve as intermediate tables. Traditionally, a nested table expression also serves this purpose. However, a common table expression can be referenced multiple times after it is instantiated; nested table expressions cannot.

I read this as saying that the CTE is only evaluated once, instantiated, and then used multiple times. Also, if the CTE is referenced only one time, the "instantiation" is optimized away.

Note that this is the way that Postgres handles CTEs (materialized subqueries) and not the way the SQL Server handles them.

Upvotes: 0

mustaccio
mustaccio

Reputation: 19004

As @Vladimir Oselsky has mentioned, only looking at the execution plan will give you a definite answer. In this contrived example the CTE subselect will likely run twice.

Upvotes: 1

Related Questions