Hi there,

What is the difference between a view and a temp table?

If I just want to simplify my queries, I mean instead of making sub queries, I would like to create a view(or temp table) first, in terms of performance, is there any difference?

Thanks,
Jinse

A view is not a real table. It is simply a "filter" on one or more real tables that is converted to an single SQL statement that accesses the real base table at execution time. This is different than a view in some other databases, which are actually materialized as a separate table like a DB2 MQT table.

A temporary table is a real table that is created within an application program, and goes away when the applicaiton is finished. It is faster than creating a regular table and then dropping it within an application (becasue the temp table is not recorded in the DB2 catalog). Each instance of an application can have its own version of the temp table without having to come up with a unique name for the table. Only the application thread that creates the temporary table can access it.

I don't quite understand this sentence. Are you saying some db stores data in the view?


Please allow me to summarize:
1) View is just a definition, it is not storing any data. So every time it is used, it queries data from the real source table. If the data in source table changes, you will see the changes from the view as well. In terms of performance, it is not going to help, maybe worse? BTW, a view exists after the session.

2) Temp table is really storing the data, so it faster the performance, but it only exist within the session. And if the data in source table changes, you won't automatically see the changes from the temp table unless you re-create it.

Is it correct?

Thanks,
Jinse

1. You are mostly correct. There is no signifigant performance issue for views. Once created, a view is permanant until it is dropped or one of the reference objects is dropped. Please refer to the SQL Reference Vol 2, CREATE VIEW for examples.

2. When a temp table is created (just like when a regular table that is created) it has no data in it. Please refer to the SQL Reference Vol 2, for DECLARE GLOBAL TEMPORARY TABLE for examples.

Your may also be interested in Materialized Query Tables.

A materialized query table is a table whose definition is based on the result of a query. As such, the materialized query table typically contains pre-computed results based on the data existing in the table or tables that its definition is based on. If the SQL compiler determines that a query will run more efficiently against a materialized query table than the base table or tables, the query executes against the materialized query table, and you obtain the result faster than you otherwise would.

Please refer to the Administration Implementation Guide, Creating a materialized query table.