April 27, 2007

Compress table partition

If some partitions of a table is read only, you can compress the partitions to 1) Save space 2)Improve performance

Command:
To compress only one partition

ALTER TABLE a_table MOVE PARTITION a_partition COMPRESS;
or
To compress whole table
ALTER TABLE a_table MOVE COMPRESS

To Check the compress information:
SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION FROM TAB_PARTITIONS;

To uncompresstable:
alter table a_table move uncompress;

Compression Result on One of my table:

Before: 13G, After 7.5G

This Operation will make the partition index unusable. It will cause the whole index become invalid. The CBO just wont consider this index.

Use the following command to rebuild partition index:
alter index ... rebuild partition p032003 nologging
This will rebuild the partition index

- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXIX