While a transaction date or timestamp is frequently a good choice for the partitioning column, consider other choices for other categories of workloads and data.
Consider a table with detailed information about telephone calls maintained by a telecommunications company. For each outgoing call, this table stores the originating telephone number, a timestamp for the beginning of the call, and the duration of the call.
Rows are retained for a variable length of time, but rarely for more than six weeks. Retention is based on the call date and the monthly bill preparation date. The primary index is a composite of the telephone number and the call-start timestamp. This column set implies that the index was chosen to provide good distribution, not to facilitate data access, and also that the likelihood of any direct primary index joins is remote.
Query Workload
There are queries that analyze all calls from a telephone number and queries that analyze all calls for a period of time for customers meeting certain criteria.
Problem Statement
The current definition of the call detail table does not use a partitioned primary index:
CREATE TABLE calldetail ( phone_number DECIMAL(10) NOT NULL, call_start TIMESTAMP, call_duration INTEGER, call_description VARCHAR(30)) PRIMARY INDEX (phone_number, call_start);
Can the standard query workloads against this table be optimized by partitioning its primary index?