As you decide to partition a table, consider the following questions regarding how you access the data:
Do you want to query data by logical grouping without using an index?
If so, this is a good candidate for partitioning. When the table is not partitioned, a table scan needs to traverse the entire table to retrieve the requested data since there is no index to restrict the data retrieved. However, for a partitioned table, a partition scan can be performed without an index and the data retrieved restricted to one or multiple partitions based on the query.
Do the queries of the application typically use the partition aligned columns to choose data?
If so, it is a good column to partition on since this will facilitate partition pruning which is where much of the performance advantage of partitioning originates. Not only does it restrict the data that needs to be scanned, but it can also lessen contention at the block level.
Do you typically search or sort on non-partition aligned fields?
If so, a global index is usually created to improve performance by avoiding the need to perform a sort operation on the retrieved data. However, maintaining a global index has a higher maintenance cost since its concurrency during certain maintenance operations affects the entire table, not just a partition of the table. Global indexes are also larger since they contain index data for all partitions of the partitioned table making maintenance operation take longer.
Do you want to query data by logical grouping without using an index?