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

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