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

Q.  Which type of partition will be done for the following?

1. Partition description identifying partition bounds.

- Published on 12 Aug 15

a. Range Partition
b. Hash Partition
c. List Partition
d. Range-Hash Partition

ANSWER: Range Partition


  • 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

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