Compressing Partition (D1_MSRMT table only)
To compress a partition, perform the steps below:
1. Create Compressed Partition Tablespace.
CREATE BIGFILE TABLESPACE CM_D1T298_P2011JAN_C DATAFILE ‘+DATADG' SIZE 50M
AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
Note: Perform Steps 2 - 9 for each subpartition (S01 – SMAX)
2. Create and Load Data Into Staging Table.
CREATE TABLE D1_MSRMT_P2011JAN_S01 PARALLEL NOLOGGING TABLESPACE CM_D1T298_P2011JAN_C
AS
SELECT /*+ PARALLEL */ * FROM D1_MSRMT SUBPARTITION(P2011JAN_S01)
ORDER BY MEASR_COMP_ID, MSRMT_DTTM;
3. Enable Logging on Newly Created Staging Table.
ALTER TABLE D1_MSRMT_P2011JAN_S01 NOPARALLEL LOGGING;
4. Create Primary Unique Index on Staging Table.
CREATE UNIQUE INDEX D1T298P0_P2011JAN_S01
ON D1_MSRMT_P2011JAN_S01(MEASR_COMP_ID, MSRMT_DTTM)
PARALLEL NOLOGGING COMPRESS ADVANCED LOW TABLESPACE CM_D1T298_P2011JAN_C;
5. Create Primary Key Constraint on Staging Table.
ALTER TABLE D1_MSRMT_P2011JAN_S01 ADD CONSTRAINT D1T298P0_P2011JAN_S01 PRIMARY KEY(MEASR_COMP_ID, MSRMT_DTTM) USING INDEX;
6. Enable Logging on Primary Key Index.
ALTER INDEX D1T298P0_P2011JAN_S01 NOPARALLEL LOGGING;
7. Exchange D1_MSRMT Table Subpartition With Newly Created Staging Table.
ALTER TABLE D1_MSRMT EXCHANGE SUBPARTITION(P2011JAN_S01) WITH TABLE D1_MSRMT_P2011JAN_S01 INCLUDING INDEXES;
Note: Ensure that steps 2-9 have been executed for each subpartition (S01 – SMAX) before continuing:
8. Drop Original Uncompressed Tablespace.
DROP TABLESPACE CM_D1T298_P2011JAN INCLUDING CONTENTS AND DATAFILES;
9. Change Partition Metadata to Reflect Compression Tablespace.
ALTER TABLE D1_MSRMT MODIFY DEFAULT ATTRIBUTES FOR PARTITION P2011JAN TABLESPACE CM_D1T298_P2011JAN_C;
10. Rename Tablespace to Original Tablespace Name.
ALTER TABLESPACE CM_D1T298_P2011JAN_C RENAME TO CM_D1T298_P2011JAN;