Lets try it !


This page demonstrates creation of ranges on the second fact table

Operation


  1. Create table 'subscriber_dedicated_account_usage_range' enabled with range partitioning
  2. Populate data into the table. Data is stored in separate ranges based on the partitioning key specified


Best Practices


  1. 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
  2. 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
  3. 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.
  4. Consider separating range partitions in separate table spaces to facilitate backup and recovery
  5. Ensure and maintain the clustering of data by making the range-partitioning key the leading column in a clustered index.