Can anyone confirm if there is there any advantage within DB2 LUW when specifying tablespace containers for indexes to have each index in its own container? Thinking about it from a maintenance as well as access perspective it would seem to be the prudent way to go. Some shops seem to bundle them up into one singe tablespace.

thanks

There may be, as it allows you to allocate separate physical resources: storage and/or memory (via separate bufferpools) to some indexes or groups of indexes. Whether it makes sense in your circumstances, only you can decide.

You can't directly specify different containers for indexes, but you can specify a different tablespace (which obviously has different containers than the other tablespaces.

There usually is not much a performance difference if you are only looking at the disk I/O aspects, but in order to use different bufferpools for tables and indexes, they must be in different tablespaces. For that reason, I always specify different tablespaces for the table and the indexes. If the size of the entire database is larger than I can allocate for bufferpools, then I aim for a higher bufferpool hit ratio for indexes than for table data.

Thanks Marcus, please clarify. If I have 5 indexes against a very large table (1.7 billion rows) you see no performance (I/O) increase by creating 5 tablespaces for each of the indexes or at least a grouping of indexes in their own tablespace?

Hmm. Since v9.7 you can assign a dedicated tablespace for 1 index, but I do not know if if also works for non-partitioned tables.

Marcus_a is right and I want to add that you can also use another page-size for your index-tablespace. I think that a 4k tablespace in good for indexes and your data could require a larger pagesize.

The only reason why I would use separate tablespaces for each index, is if I was considering putting them in separate bufferpools (either now or in the future). There should not be much, if any, noticeable phyiscal I/O difference, if the indexes are not already in the bufferpool (hopefully not often). DB2 doesn't often use more than one index at a time, but there may be situations where it does use more than one index depending on your database and application design.

Thanks. Good response. Could you expand upon the advantages of separating Data & Indexes in their own tablespaces.

My apologies, regarding my last post, does this methodology imply to both OLTP and DW environments?

thanks again

Very nice discussion on index container.

The mostly reason for put the indexes or/data in separated tablespaces is for holding them in separated bufferpool。If you put them together in one bufferpool,Maybe some indexes or data pages you want to pin in the memory will be chose as victim pages to flush out to disk。The best way to avoid it is to put each indexes and tables in a seperated tablespaces ，but There will be too many table spaces and bufferpools to maintain。SO how may tablespaces , bufferpools you will create is depend on the specific situations