Partition : some more . ..

Some More …………..

Ø  Adding Values for a List Partition:

ALTER TABLE sales_by_region

   MODIFY PARTITION region_south

      ADD VALUES ('OK', 'KS');

Ø  Adding Values for a List Subpartition

ALTER TABLE quarterly_regional_sales

   MODIFY SUBPARTITION q1_1999_southeast

      ADD VALUES ('KS');

Ø  Dropping Values from a List Partition

ALTER TABLE sales_by_region

   MODIFY PARTITION region_south

      DROP VALUES ('OK', 'KS');

Ø  Moving Table Partitions

ALTER TABLE parts MOVE PARTITION depot2

     TABLESPACE ts094 NOLOGGING COMPRESS;

Ø  Renaming Partitions

ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;

ALTER INDEX scuba RENAME SUBPARTITION sys_subp3254 TO bcd_types;

Ø  Truncating Partitions (11g)

·       Truncating a partition is like dropping a partition, except that the partition is emptied of its data, but not physically dropped.

·       You cannot truncate an index partition. However, if local indexes are defined for the table, the ALTER TABLE ... TRUNCATE PARTITION statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt.

Ø  In this example, table sales has a global index sales_area_ix, which is rebuilt

ALTER TABLE sales TRUNCATE PARTITION dec98;

ALTER INDEX sales_area_ix REBUILD;

Ø  This causes the global index to be truncated at the time the partition is truncated.

ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;

Table Compression and Partitioning

The following statement moves and compresses an existing partition sales_q1_1998 of table sales:

Ø  ALTER TABLE sales

MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;

Ø  ALTER TABLE sales

MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998

COMPRESS FOR ARCHIVE LOW;

If you use the MOVE statement, then the local indexes for partition sales_q1_1998 become unusable. You must rebuild them afterward, as follows:

Ø  ALTER TABLE sales

MODIFY PARTITION sales_q1_1998 REBUILD UNUSABLE LOCAL INDEXES;

You can also include the UPDATE INDEXES clause in the MOVE statement in order for the entire operation to be completed automatically without any negative effect on users accessing the table.

The following statement merges two existing partitions into a new, compressed partition, residing in a separate table space. The local bitmap indexes have to be rebuilt afterward, as in the following:

Ø  ALTER TABLE sales MERGE PARTITIONS sales_q1_1998, sales_q2_1998

INTO PARTITION sales_1_1998 TABLESPACE ts_arch_1_1998

COMPRESS FOR OLTP UPDATE INDEXES;