Range Partition - Features,Properties - Oracle Indexes and Partitioned Tables

Discussion

• Nirja Shah   -Posted on 28 Sep 15
  1. Range Partitioning
  - Use range partitioning to map rows to partitions based on ranges of column values.
  - This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year.
  - Performance is best when the data evenly distributes across the range.
  - If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning.
  - When creating range partitions, you must specify:
  1. Partitioning method: range
  2. Partitioning column(s)
  3. Partition descriptions identifying partition bounds
  
  2. Range-Hash Partitioning
  - It partitions data using the range method, and within each partition, subpartitions it using the hash method.
  - These composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
  
  - When creating range-hash partitions, you specify the following:
  1. Partitioning method: range
  2. Partitioning column(s)
  3. Partition descriptions identifying partition bounds
  4. Subpartitioning method: hash
  5. Subpartitioning column(s)
  6. Number of subpartitions for each partition or descriptions of subpartitions
  
  3. Hash partitioning
  - Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables).
  - This is typically used where ranges aren't appropriate, i.e. employee number, productID, etc.
  
  4. List partitioning
  - List partitioning in MySQL is similar to range partitioning in many ways.
  - As in partitioning by RANGE, each partition must be explicitly defined.
  - The chief difference between the two types of partitioning is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values.
  - This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers.

➨ Post your comment / Share knowledge