Is there any performance benefit in UDB on Unix if we keep the indexes on a different tablespace ? For, the indexes on the primary key we do not explicitly create the indexes so how can we put these into a separate tablespace ?

Hi Dipanjan,

It is a good recommended practise to seperate indexes if you are using DMS tablespaces because that improves the performance as well as you are keeping different entities seperate from the start.

When defining the table if you specify index in <tablespace name> that will ensure that the indexes go into the different tablespace so even the pk index will go there.

Hope I am right

If you place the indexes in a different tablespace from the data, this allows you to place the tablespaces on different physical disk drives, or different striped arrays. It also gives the opportunity (if desired) to use different bufferpools for the data and indexes.

Whether or not these things improve performance, and by how much, depends on many factors about the nature of the application and the size of the bufferpools.

The major advantage of keeping indexes separate, preferably on a different physical device is efficient I/O ...

IMHO, you need not put tables and indexes on different tablespaces, if the tables are small and/or not frequently inserted/updated ...

HTH

Sathyaram