Hey all,

I'm pretty sure of what you're gonna say, but I have to ask anyways.

Is it possible to index a view?

As I understand it, when used in a query, the view is generated then used as a table and joined with the other tables/views in the query. So it is easy to optimize the generation of the view, but when used in a query with other tables/views, I can't index the view for better response, can I?

Thanks.

JFS.

Nope, no index on a view.

Andy

You are not understanding how DB2 works with views. Views are not materialized and it would not help to index them. When you have a view, the SQL is re-written (and optimized) by DB2 when the statement is compiled to a new SQL statement without the view against the base tables, and any indexes on the base tables will be used.

A view in SQL (not just DB2's dialect) is just like a macro. You can think of it as being some simple text replacement occurring to resolve the view before the actual execution of the statement starts. (It is not that simple because of things like privileges, but you should get the idea.)

Yes, it is. That is to say, "it depends":
With DB2 9 for z/OS you can define an index on an expression, hence a view having a column which is not just a base table column cannot be indexed in earlier versions of DB2 but can be in DB2 9.
(So it's not the view itself which gets indexed, but the result is the same when the view is going to be queried.)