For
example, you may only want to compress the older, less often accessed partitions of a partitioned
table and leave the most recent partition uncompressed to minimize the CPU overhead for retrieval
of recent data. In this example, you will create a new version of the CAT_REQ table you created
earlier in this chapter, compressing the first two partitions only. Here is the SQL command:
create table cat_req_2
(cat_req_num number not null,
cat_req_dt date not null,
cat_cd number not null,
cust_num number null,
req_nm varchar2(50),
req_addr1 varchar2(75),
req_addr2 varchar2(75),
req_addr3 varchar2(75))
partition by range (cat_req_dt)
(partition cat_req_spr_2007
values less than (to_date('20070601','YYYYMMDD'))
tablespace prd01 compress,
partition cat_req_sum_2007
values less than (to_date('20070901','YYYYMMDD'))
tablespace prd02 compress,
partition cat_req_fal_2007
values less than (to_date('20071201','YYYYMMDD'))
tablespace prd03 nocompress,
partition cat_req_win_2008
values less than (maxvalue)
tablespace prd04 nocompress);
You do not have to specify nocompress, because it is the default. To find out which partitions
are compressed, you can use the column COMPRESSION in the data dictionary table DBA_TAB_
PARTITIONS, as you can see in the following example:
SQL> select table_name, partition_name, compression
2 from dba_tab_partitions
3 where table_name = 'CAT_REQ_2';
TABLE_NAME PARTITION_NAME COMPRESS
---------------- -------------------- --------
CAT_REQ_2 CAT_REQ_FAL_2007 DISABLED
CAT_REQ_2 CAT_REQ_SPR_2007 ENABLED
CAT_REQ_2 CAT_REQ_SUM_2007 ENABLED
CAT_REQ_2 CAT_REQ_WIN_2008 DISABLED
4 rows selected.
Pages:
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850