Adding partitions - Range, Hash, List, Composite(range/hash)- Oracle Indexes and Partitioned Table

Q.  In ALTER TABLE Maintenance Operations for Table Partitions which maintenance operation partition is used by range, hash, list, composite(range/hash), composite(range/list) from the following?

- Published on 19 Oct 15

a. Coalescing Partitions
b. Dropping Partitions
c. Adding partitions
d. Both A & B

ANSWER: Adding partitions
 

    Discussion

  • Nirja Shah   -Posted on 29 Sep 15
    - Partitions and subpartition maintenance operations are performed for both tables and indexes.

    - For each type of partitioning and subpartitioning, the specific clause of the ALTER TABLE statement that is used to perform that maintenance operation is listed.

    Adding Partitions

    - It describes how to manually add new partitions to a partitioned table and explains why partitions cannot be specifically added to most partitioned indexes.

    - It is used to add partitions by range, hash, list, composite(range/hash), composite(range/list).

    Dropping Partitions

    - You can drop partitions from range, interval, list, or composite *-[range | list] partitioned tables. For interval partitioned tables, you can only drop range or interval partitions that have been materialized.

    - For hash-partitioned tables, or hash subpartitions of composite *-hash partitioned tables, you must perform a coalesce operation instead.

    - You cannot drop a partition from a reference-partitioned table. Instead, a drop operation on a parent table cascades to all descendan tables.

    Coalescing Partitions

    - Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table or index, or the number of subpartitions in a *-hash partitioned table.

    - When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function.

    - The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed.

    - If you coalesce a hash partition or subpartition in the parent table of a reference-partitioned table definition, then the reference-partitioned table automatically inherits the new partitioning definition.

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.)