Hello
I've got a table that holds data for the last 6 days. It is loaded at the end of every day with the current day's data from a variety of sources. I've range partitioned it based on a date and sub partitioned based on a list of values using a template. As the table is currently 30-40gb, with each day's worth of data taking 6-7gb I'd like to compress everything except the "current" partition:
CREATE TABLE dt_test_t1
( id number not null,
a_date date not null,
a_code varchar2(2) not null,
a_column1 VARCHAR2(1000),
a_column2 VARCHAR2(1000),
a_column3 VARCHAR2(1000),
a_column4 VARCHAR2(1000)
)
PARTITION BY RANGE (a_date)
SUBPARTITION BY LIST(a_code)
SUBPARTITION TEMPLATE
( SUBPARTITION p_Code01 VALUES('01'),
SUBPARTITION p_Code02 VALUES('02'),
SUBPARTITION p_Code03 VALUES('03')
)
( PARTITION p_20070101 VALUES LESS THAN(TO_DATE('02-JAN-2007','DD-MON-YYYY')) TABLESPACE users COMPRESS,
PARTITION p_20070102 VALUES LESS THAN(TO_DATE('03-JAN-2007','DD-MON-YYYY')) TABLESPACE users COMPRESS,
PARTITION p_20070103 VALUES LESS THAN(TO_DATE('04-JAN-2007','DD-MON-YYYY')) TABLESPACE users COMPRESS,
PARTITION p_20070104 VALUES LESS THAN(TO_DATE('05-JAN-2007','DD-MON-YYYY')) TABLESPACE users COMPRESS,
PARTITION p_20070105 VALUES LESS THAN(TO_DATE('06-JAN-2007','DD-MON-YYYY')) TABLESPACE users
)
/
Which works fine. As this is a rolling window, I'd like to add a new uncompressed partition, and then compress the previously current partition, but I can't seem to get it right. I can't use
tylerd@DEV2> alter table dt_test_t1 move partition p_20070105 compress
2 /
alter table dt_test_t1 move partition p_20070105 compress
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
and I can't compress the sub partitions individually
tylerd@DEV2> alter table dt_test_t1 move partition p_20070105_p_Code01 compress
2 /
alter table dt_test_t1 move partition p_20070105_p_Code01 compress
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
Elapsed: 00:00:00.01
tylerd@DEV2> alter table dt_test_t1 move subpartition p_20070105_p_Code01 compress
2 /
alter table dt_test_t1 move subpartition p_20070105_p_Code01 compress
*
ERROR at line 1:
ORA-14160: this physical attribute may not be specified for a table subpartition
I can modify the partition to be compressed, but this only effects the attribute, it doesn't actually compress the data.
tylerd@DEV2> INSERT --+ append
2 INTO
3 dt_test_t1
4 SELECT
5 rownum id,
6 sysdate - MOD(rownum, 5),
7 TO_CHAR(MOD(rownum,3)+1,'fm09'),
8 LPAD('*',1000,'*'),
9 LPAD('*',1000,'*'),
10 LPAD('*',1000,'*'),
11 LPAD('*',1000,'*')
12 FROM
13 dual
14 CONNECT BY
15 LEVEL <= 10000
16 /
10000 rows created.
Elapsed: 00:00:00.32
tylerd@DEV2> SELECT
2 partition_name,
3 bytes,
4 blocks
5 FROM
6 dba_segments
7 WHERE
8 segment_name = 'DT_TEST_T1'
9 /
PARTITION_NAME BYTES BLOCKS
------------------------------ ---------- ----------
P_20070101_P_CODE01 131072 8
P_20070101_P_CODE02 131072 8
P_20070101_P_CODE03 131072 8
P_20070102_P_CODE01 131072 8
P_20070102_P_CODE02 131072 8
P_20070102_P_CODE03 131072 8
P_20070103_P_CODE01 131072 8
P_20070103_P_CODE02 131072 8
P_20070103_P_CODE03 131072 8
P_20070104_P_CODE01 131072 8
P_20070104_P_CODE02 131072 8
P_20070104_P_CODE03 131072 8
P_20070105_P_CODE01 4194304 256
P_20070105_P_CODE02 4194304 256
P_20070105_P_CODE03 4194304 256
15 rows selected.
Elapsed: 00:00:00.06
tylerd@DEV2> ALTER TABLE dt_test_t1 MODIFY PARTITION P_20070105 COMPRESS
2 /
Table altered.
Elapsed: 00:00:00.01
tylerd@DEV2> SELECT
2 partition_name,
3 bytes,
4 blocks
5 FROM
6 dba_segments
7 WHERE
8 segment_name = 'DT_TEST_T1'
9 /
PARTITION_NAME BYTES BLOCKS
------------------------------ ---------- ----------
P_20070101_P_CODE01 131072 8
P_20070101_P_CODE02 131072 8
P_20070101_P_CODE03 131072 8
P_20070102_P_CODE01 131072 8
P_20070102_P_CODE02 131072 8
P_20070102_P_CODE03 131072 8
P_20070103_P_CODE01 131072 8
P_20070103_P_CODE02 131072 8
P_20070103_P_CODE03 131072 8
P_20070104_P_CODE01 131072 8
P_20070104_P_CODE02 131072 8
P_20070104_P_CODE03 131072 8
P_20070105_P_CODE01 4194304 256
P_20070105_P_CODE02 4194304 256
P_20070105_P_CODE03 4194304 256
15 rows selected.
Elapsed: 00:00:00.07
I understand that modifying an attribute of the partition isn't going to initiate a direct path operation that will allow the compression to take place, so the question is, how do I compress the data in P_20070105? Is the only option to exchange it to a table, compress it and then re-exchange it?
tylerd@DEV2> CREATE TABLE dt_test_t1_exch
2 ( id number not null,
3 a_date date not null,
4 a_code varchar2(2) not null,
5 a_column1 VARCHAR2(1000),
6 a_column2 VARCHAR2(1000),
7 a_column3 VARCHAR2(1000),
8 a_column4 VARCHAR2(1000)
9 )
10 PARTITION BY LIST(a_code)
11 ( PARTITION p_Code01 VALUES('01'),
12 PARTITION p_Code02 VALUES('02'),
13 PARTITION p_Code03 VALUES('03')
14 )
15 TABLESPACE users COMPRESS
16 /
Table created.
Elapsed: 00:00:00.06
tylerd@DEV2> ALTER TABLE dt_test_t1 EXCHANGE PARTITION P_20070105
2 WITH TABLE dt_test_t1_exch COMPRESS
3 /
WITH TABLE dt_test_t1_exch COMPRESS
*
ERROR at line 2:
ORA-14094: invalid ALTER TABLE EXCHANGE PARTITION option
Elapsed: 00:00:00.01
tylerd@DEV2> ALTER TABLE dt_test_t1 EXCHANGE PARTITION P_20070105
2 WITH TABLE dt_test_t1_exch
3 /
Table altered.
Elapsed: 00:00:00.01
tylerd@DEV2> SELECT
2 partition_name,
3 bytes,
4 blocks
5 FROM
6 dba_segments
7 WHERE
8 segment_name = 'DT_TEST_T1_EXCH'
9 /
PARTITION_NAME BYTES BLOCKS
------------------------------ ---------- ----------
P_CODE01 4194304 256
P_CODE02 4194304 256
P_CODE03 4194304 256
3 rows selected.
Elapsed: 00:00:00.04
tylerd@DEV2> ALTER TABLE dt_test_t1_exch COMPRESS
2 /
Table altered.
Elapsed: 00:00:00.01
tylerd@DEV2> SELECT
2 partition_name,
3 bytes,
4 blocks
5 FROM
6 dba_segments
7 WHERE
8 segment_name = 'DT_TEST_T1_EXCH'
9 /
PARTITION_NAME BYTES BLOCKS
------------------------------ ---------- ----------
P_CODE01 4194304 256
P_CODE02 4194304 256
P_CODE03 4194304 256
3 rows selected.
Elapsed: 00:00:00.03
tylerd@DEV2> ALTER TABLE dt_test_t1_exch MOVE COMPRESS
2 /
ALTER TABLE dt_test_t1_exch MOVE COMPRESS
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
Elapsed: 00:00:00.04
tylerd@DEV2> ALTER TABLE dt_test_t1_exch MOVE PARTITION p_Code01 COMPRESS
2 /
Table altered.
Elapsed: 00:00:00.23
tylerd@DEV2> ALTER TABLE dt_test_t1_exch MOVE PARTITION p_Code02 COMPRESS
2 /
Table altered.
Elapsed: 00:00:00.18
tylerd@DEV2> ALTER TABLE dt_test_t1_exch MOVE PARTITION p_Code03 COMPRESS
2 /
Table altered.
Elapsed: 00:00:00.14
tylerd@DEV2> SELECT
2 partition_name,
3 bytes,
4 blocks
5 FROM
6 dba_segments
7 WHERE
8 segment_name = 'DT_TEST_T1_EXCH'
9 /
PARTITION_NAME BYTES BLOCKS
------------------------------ ---------- ----------
P_CODE01 131072 8
P_CODE02 131072 8
P_CODE03 131072 8
3 rows selected.
Elapsed: 00:00:00.03
tylerd@DEV2> ALTER TABLE dt_test_t1 EXCHANGE PARTITION P_20070105
2 WITH TABLE dt_test_t1_exch
3 /
Table altered.
Elapsed: 00:00:00.04
tylerd@DEV2> SELECT
2 partition_name,
3 bytes,
4 blocks
5 FROM
6 dba_segments
7 WHERE
8 segment_name = 'DT_TEST_T1'
9 /
PARTITION_NAME BYTES BLOCKS
------------------------------ ---------- ----------
P_20070101_P_CODE01 131072 8
P_20070101_P_CODE02 131072 8
P_20070101_P_CODE03 131072 8
P_20070102_P_CODE01 131072 8
P_20070102_P_CODE02 131072 8
P_20070102_P_CODE03 131072 8
P_20070103_P_CODE01 131072 8
P_20070103_P_CODE02 131072 8
P_20070103_P_CODE03 131072 8
P_20070104_P_CODE01 131072 8
P_20070104_P_CODE02 131072 8
P_20070104_P_CODE03 131072 8
P_20070105_P_CODE01 131072 8
P_20070105_P_CODE02 131072 8
P_20070105_P_CODE03 131072 8
15 rows selected.
Elapsed: 00:00:00.07
It doesn't really matter if the new partition I add is compressed or uncompressed because the data isn't being inserted using direct path anyway, the point is that I'm going to need to compress the data in the partition after it has been loaded. It just seems a bit odd that this can be done with a single command for a range or hash partitioned table, but you have to jump through hoops for a composit partitioned table. Have I missed something or is this the only method?
David