DB2

There's an object called index view since MS SQL Server 2000, it's an index which is created on an view which is based on a local table. it is attent to imporve the select performence on the view when this view is based on multi-table and has complex logic.

I try to find a simular thing in DB2, but there's no the simular solution in DB2 v8.2 for now. Instead, I found another thing instested.

DB2 allows creating index on the tables or views in remote data source, such as a database on another host or an XML data source. To be exactly, that's Not true Index, it's Index Specification. It requests that the tables in remote data source have created index in its own system. And it only repeat the index description in local system.

Local DB2 Env                                            Remote DB2 Env

Index Specification    ->    Nickname    - - ->   True Index    ->    Table

See following example in DB2 info center.

• CREATE INDEX statement
  http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0000919.htm?resultof=%22%63%72%65%61%74%65%22%20%22%63%72%65%61%74%22%20%22%69%6e%64%65%78%22%20%22%73%74%61%74%65%6d%65%6e%74%22%20
  

Example 3:  The nickname EMPLOYEE references a data source table called CURRENT_EMP. After this nickname was created, an index was defined on CURRENT_EMP. The columns chosen for the index key were WORKDEBT and JOB. Create an index specificationindex. Through this specification, the optimizer will know that the index exists and what its key is. With this information, the optimizer can improve its strategy to access the table. that describes this

   CREATE UNIQUE INDEX JOB_BY_DEPT
     ON EMPLOYEE (WORKDEPT, JOB)
     SPECIFICATION ONLY        

Note: the Nickname is only allowed to be created on tables in local database and on much kind of objects at remote data source. It's not allowed to be created on views in local database.