Index-organized tables partitioning methods - 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. 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.
  
  3. 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