We have a table with tens of millions of time series rows. We index by three foreign keys to relations. Let's hypothetically say medical observation row to doctor, acl, and person. Main thing being a 3 part key where acl is based on the user and the other two based on entity relationship.
What's a good physical architecture in db2 to make this run fast? (sub second) It currently takes 10+ seconds to query.
- RAM usage is around 25-30%
- Read hits to buffer pool are around -1300%
- Log hits to buffer pool are 100% (are we doing too many transactions when we don't need to - i.e. change to read uncommitted)
I wonder about having a separate table space for this with a separate buffer pool for example.
Also would you consider partitioning?