If a partitioned table has global indexes, DDL operations can be performed on the partitioned table.

Syntax

ALTER TABLE relation_expr alter_partition_cmd  [update_global_index_clause]
  alter_partition_cmd = { DROP PARTITION partition_name |  
  DROP PARTITION FOR partition_values |
  ADD_P PARTITION partition_name table_partition_description |.....}
  update_global_index_clause = { UPDATE | INVALIDATE } GLOBAL INDEXES}

Description

  • UPDATE GLOBAL INDEXES: If you use the UPDATE GLOBAL INDEXES clause when you perform a DDL operation on a partitioned table, all global indexes for the partitioned table are updated. The current version does not support the UPDATE GLOBAL INDEXES clause. If you use this clause when you perform a DDL operation, an error is reported.
  • INVALIDATE GLOBAL INDEXES: If you use the INVALIDATE GLOBAL INDEXES clause when you perform a DDL operation on a partitioned table, all global indexes for the partitioned table are invalidated.
  • If you use neither of the two clauses when you perform a DDL operation on a partitioned table, all indexes for the specified partitioned table are invalidated. If you include TRUNCATE, CLUSTER, or VACUUM FULL in your DDL statement, all global indexes for the specified partitioned table are invalidated.
  • If a partitioned table has global indexes, you can perform the following DDL operations:
    • drop partition
    • split partition
    • merge partition
    • exchange partition
    • truncate partition
    • truncate child table
    • cluster/vacuum full child table
  • If you use the INVALIDATE GLOBAL INDEXES clause when you perform one of the following DDL operations, global indexes for the specified partitioned table are not invalidated.
    • add partition
    • move partition

Examples

  • add partition
    ALTER TABLE gi_ora ADD PARTITION p4 VALUES LESS THAN(400);
    
    ALTER TABLE gi_ora ADD PARTITION p4 VALUES LESS THAN(400) UPDATE GLOBAL INDEXES;
    
    ALTER TABLE gi_ora ADD PARTITION p4 VALUES LESS THAN(400) INVALIDATE GLOBAL INDEXES;
    (1 row)
  • drop partition
    ALTER TABLE gi_ora DROP PARTITION p4 UPDATE GLOBAL INDEXES;
    ALTER TABLE gi_ora DROP PARTITION p4 INVALIDATE GLOBAL INDEXES;