- Enhances the performance, manageability, and availability.
- Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces.
When to partition a table:
- Tables larger than 2gb size.
- Historical data
- Contents must be stored in different storage
- Avoid index maintenance when data is removed
- Index maintenance on parts of data like rebuilding
Benefits of partitioning:
- For performance: partition pruning, partition wise joins
- For manageability: backup of single partition
- For availability: different storage device
How to partition:
- Single level partitioning:
- Range: maps data to partitions based on ranges of values
- List: a region column as the partitioning key
- Hash: data is not historical or has no obvious partitioning key.
A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.