Index-organized tables partitioning methods - Oracle Indexes and Partitioned Tables

Q.  Which partitioning methods can be used for index-organized tables?
- Published on 03 Aug 15

a. Range
b. List
c. Hash
d. All mentioned above
e. None of the above

ANSWER: All mentioned above


  • 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

Enter the code shown above:
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)