Not such good ideas

One of our consultants dropped by my desk to ask me some Oracle questions around data life cycle management. One of his customers is moving a store based system to a central system and like a lot of systems in the UK needs to keep 7 years of transactional data available. I explained all of the standard stuff: partitioning for ease of maintenance, use of lower spec disk (slower) for the old stuff you don't need often, table compression, indexing techniques.

He came back a couple days later to run past me the ideas from the customer's external consultant. The consultant proposed that the data was partitioned by geographic region. Although this idea sounds like it has merit, after all most queries will concern single stores, it rang alarm bells in my mind.

• There is a need to keep a rolling 7 year history, old data needs to be removed, new added. Maintaining partitions through delete and insert operations is not efficient. Dropping the oldest partition and adding a new one is very efficient, especially if we partition exchange to populate
• Not using time as part of the partition key prevents access to features such as readonly tablespaces
• Although each store is today allocated to a region, who's to say that next month the business won't reorganise and drop a couple of regions and reallocate the stores affected to the remaining regions. It is not sensible let data move between partitions. Ideally, a partition key should be immutable and region clearly is not