Index on Temporary tables created using WITH clause?

gvshruthi · New User Joined: 05 Jan 2009 Posts: 4 Location: bangalore

I have a legacy SQL query. This has many temporary tables, created dynamically using WITH clause.
Eg:

WITH <temp-table name> (column 1, column2) AS
(Select col1, col2 from <table name>
Where col1 = col2
)

Is it possible to create indexes on such temporary tables? These tables are created only for a particular run. Hence I am not aware if any dynamic index could be created on such tables.

Kindly help me on this.

sushanth bobby · Posted: Tue Sep 20, 2011 11:46 pm

Hi Shruthi,

Welcome to IBMMAINFRAMES!

Example you have given is not a temporary table, its a sort of a result table that can be referenced in a FROM clause of a SELECT statement that follows the WITH keyword. Its called Common Table Expression, shortly CTE.

But, Indexes can be created on a DECLARE GLOBAL TEMPORARY TABLE.

Check out DB2 V9 SQL Reference --> Chapter 5 Statements --> DECLARE GLOBAL TEMPORARY TABLE

Hope this helps,
Sushanth