Lets try it !
This page demonstrates creation of ranges on the second fact table
Operation
- Create table 'subscriber_dedicated_account_usage_range' enabled with range partitioning
- Populate data into the table. Data is stored in separate ranges based on the partitioning key specified
Best Practices
- Partition on DATE columns. Roll-in and roll-out scenarios are almost always based on dates. Improved query execution plan (QEP) selection, using partition
elimination, and a significant set of those opportunities are also based on date predicates
- Use table (range) partitioning to rapidly delete (roll-out) ranges of data. Match range-partitioning periods to roll-in and roll-out ranges. For example, if you need to roll-in and roll-out data by month, range partitioning by month is a good strategy
- Limit the number of ranges. Remember that each range is a table object with a minimum of two extents. Make the size of your ranges match the size you typically rollout.
- Consider separating range partitions in separate table spaces to facilitate backup and recovery
- Ensure and maintain the clustering of data by making the range-partitioning key the leading column in a clustered index.