We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX.
Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG data each into their own tablespace. (Reason is better control over disk, maintenance, backups, etc.)
Tablespaces have to be associated to bufferpools. Right now most of our tables and indexes fit into 4K bufferpools and tablespaces.
Normally with our scripts we set up a 4K bufferpool BP4K. We then set up at least two 4K tablespaces (as we dont' have a lot of LOBs): TS_DAT_4K for data and TS_IND_4K for indexes. By default we have just been assigning both of these tablespaces to bufferpool BP4K.
What I have wondered: Since indexes are in their own tablespace, should they also be given their own bufferpool?
My reason for wondering this is based on optimization. If indexes have their own bufferpool, then they are more likely to remain in memory (rather than being pushed out of memory due to table records being read in). This would allow for faster scanning of indexes to find the records in the tables. And since indexes would no longer share the same bufferpool with the tables, more of the tables can now remain in memory for logical retrieval versus a physical read. So my thought is that this would reduce physical I/O and thus help with the performance/maintenance of the database.
I also can't help but wondering if this is just pre-optimization, which is 99% of the time a bad thing to do, introducing extra overhead, etc. (especially since we haven't identified that we need separate bufferpools yet based on current performance. Of course the application under development isn't in production yet either and does still need database tuning....)
Thoughts on this? Is this a best-practice? or just pre-optimization and over-thinking?