As you decide to partition a table, consider the following questions regarding the data:
Does the data have a logical grouping to it?
Partitioning by logical data grouping provides the best maintenance and performance experience.
Does the data have a historical aspect to it?
In other words, does the data age over time and become less important to the day to day OLTP activities of the deployment? If so, it is a good candidate for partitioning since historical data can be marked read only, more easily deleted, archived or moved to a different storage device if the table were partitioned.
Historical data can more easily be moved to less expensive storage in a partitioned environment. The alternative but just as unique to partitioning is that historical data can age on its existing aging storage device while new data can be easily directed to new storage devices. Either way, partitioning allows you control over where each data grouping (partition) is stored.
Does the data have an organizational aspect to it?
For instance, is the data broken down by regions or some other relatively static grouping? If so, it is a good candidate for list partitioning since there are specific values that identify each logical grouping of data that can be used to define each partition.
Does the data have order to it; either a numeric order or a time stamp ordering that would logically imply a grouping of the data?
For example, do you logically think of your data as being organized by quarter or an annual basis? If so, it is a good candidate for range partitioning since there are value ranges that identify each logical grouping of data that can be used to define each partition.
Do you have more than one column that can be used to uniquely identify a group of data in your table?
The more refined the partition definition, the more you will experience the virtues of partitioning. For example, partitioning by date alone will not do much to improve block level concurrency since all new data is most likely being inserted with the same date value in the same partition for any particular day. However, sub-partitioning by region as well as by date may improve concurrency between regions.
Do the values of the partitioning columns remain the same over time?
You want to pick a column that does not change frequently since the changing the value in a partition aligned field internally requires a delete followed by an insert operation to move the record to its new partition. This also has the side effect of changing the ROWID which can be troublesome if your application uses ROWIDs to directly access data.
Does an index containing the partition columns in order, as its leading components, exist already or must one be added? What is the cost of adding such an index if it does not already exist? Can you recompile the application to take advantage of this new index?
The columns chosen to partition on must exist as the leading component of an index. This is referred to as a partition aligned local index and is required when enabling partitioning on a table.
Are the partition aligned columns well known at record creation time?
Partition aligned columns do not support the UNKNOWN “?” value. If the partition aligned fields are not well known at record creation time, the columns may not be the correct columns to be using to define your partitioning.
Partition aligned columns must be assigned in the first assign statement at record creation time, or have default values that locate the record in a defined partition. Some applications create records and populate the fields of the record across several assign statements. If one of those assign statements requires the record be physically created (as opposed to deferring the record creation) then the default values of the partition aligned column will be used to initially create the record if they are not already assigned. This has an adverse performance side effect when updating the partition aligned fields, as mentioned earlier. Worse could be that the record is prevented from being created altogether if a partition definition for locating records with the default value combinations does not exist.
Does the data have a logical grouping to it?