 | ||
| Compressing infrequently accessed table partitions | ||
|---|---|---|
| Prev | Compressed files and databases | Next |
Partitioned tables is a frequently used feature for large Oracle databases. Table partitioning improves database queries and updates because partitioning helps parallelizing transactions that use Parallel Queries. Partitioning also makes maintenance of database easy and improves the availability of TABLES. If a partition is down, only the corresponding portion of the TABLE goes offline and the rest of the TABLE remains online. In a telecommunications environment, a common practice is to partition a 'call_details' table by month or quarter. The contents in the partition are less active as the partition gets older. The new records are added to a new partition, and previous quarter records do not get updated. Since telecommunications databases are generally very large, compressing last year's data provides great savings.
In the following example, assume that the table 'CALL_DETAIL' is partitioned by quarters, and the partition names are CALL_2010_Q1, CALL_2010_Q2, and CALL_2011_Q1, and so on. In the first Quarter of 2011, you can compress the CALL_2010_Q1 data.
To compress the CALL_2010_Q1 partition
SQL> select tablespace_name from dba_tab_partitions where table_name = 'CALL_DETAIL' and partition_name = 'CALL_2010_Q1';
Assume that the query returns "TBS_2010_Q1".
my_compress_files file:SQL> select file_name from dba_data_files where tablespace_name = 'TBS_2010_Q1';
Store the result in the my_compress_files file.
$ /opt/VRTS/bin/vxcompress '/bin/cat my_compress_files'