Hello Team, I was trying to implement the "compress for query high" option in a table where 700 million data (300 GB) per month.
I would like to know why the compression does not work though table/sub partitions are defined with compress for query high. The temp table (data after reformat using source file) size is 32 MB in below example and that shows same size after moved the data to compressed table. The sub partition table still shows COMPRESSION is enabled but size is not reduced. I know, we can use additional "alter table move " command to compress existing data, but that takes me 8+ hrs on 700 million data. So I am trying to achieve same using exchange partition.
Below is an example what exactly I followed in my work. Can anyone please help?
Script:
drop TABLE MYTEST_TMP;
CREATE TABLE MYTEST_TMP (MYTEST_id number(15), ACCT_YR_MO_NO varchar2(10), POLICY_VERSION_ID number, policY_no number) COMPRESS FOR QUERY HIGH
PARTITION BY HASH ("POLICY_VERSION_ID") PARTITIONS 4;
INSERT INTO MYTEST_TMP SELECT LEVEL MYTEST_id, '2018/02' ACCT_YR_MO_NO , LEVEL POLICY_VERSION_ID, LEVEL policY_no FROM DUAL CONNECT BY LEVEL < 100000;
commit;
create unique index uidx_MYTEST_TMP on MYTEST_TMP(ACCT_YR_MO_NO,POLICY_VERSION_ID,MYTEST_id) local;
drop TABLE MYTEST;
CREATE TABLE MYTEST (MYTEST_id number(15), ACCT_YR_MO_NO varchar2(10), POLICY_VERSION_ID number, policY_no number) COMPRESS FOR QUERY HIGH
PARTITION BY RANGE ("ACCT_YR_MO_NO")
SUBPARTITION BY HASH ("POLICY_VERSION_ID")
(PARTITION "P201802" VALUES LESS THAN ('2018/03') COMPRESS FOR QUERY HIGH SUBPARTITIONS 4);
create unique index uidx_MYTEST on MYTEST(ACCT_YR_MO_NO,POLICY_VERSION_ID,MYTEST_id) local;
select segment_type,segment_name,
trunc(sum(gb)) in_GB, trunc(sum(MB)) in_MB, trunc(sum(KB)) in_KB
from (
select segment_name, segment_type, bytes/1024/1024/1024 GB, bytes/1024/1024 MB , bytes/1024 KB
from user_SEGMENTS
where segment_name LIKE '%MYTEST%'
)
group by segment_type, segment_name
ORDER BY 3 DESC;
alter table MYTEST exchange partition P201802 with table MYTEST_TMP including indexes with validation;
SELECT TABLE_NAME, COMPOSITE, PARTITION_NAME, COMPRESSION, COMPRESS_FOR FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='MYTEST';
SELECT TABLE_NAME, SUBPARTITION_NAME, COMPRESSION, COMPRESS_FOR FROM ALL_TAB_SUBPARTITIONS WHERE TABLE_NAME='MYTEST';
select segment_type,segment_name,
trunc(sum(gb)) in_GB, trunc(sum(MB)) in_MB, trunc(sum(KB)) in_KB
from (
select segment_name, segment_type, bytes/1024/1024/1024 GB, bytes/1024/1024 MB , bytes/1024 KB
from user_SEGMENTS
where segment_name LIKE '%MYTEST%'
)
group by segment_type, segment_name
ORDER BY 3 DESC;
SELECT TABLE_NAME, COMPOSITE, PARTITION_NAME, COMPRESSION, COMPRESS_FOR FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='MYTEST';
SELECT TABLE_NAME, SUBPARTITION_NAME, COMPRESSION, COMPRESS_FOR FROM ALL_TAB_SUBPARTITIONS WHERE TABLE_NAME='MYTEST';
Logs:
---------------------------------------------------------------
Table MYTEST_TMP dropped.
Table MYTEST_TMP created.
99,999 rows inserted.
Commit complete.
Index UIDX_MYTEST_TMP created.
Table MYTEST dropped.
Table MYTEST created.
Index UIDX_MYTEST created.
SEGMENT_TYPE SEGMENT_NAME IN_GB IN_MB IN_KB
------------------ -------------------------- ---------- ----------
INDEX PARTITION UIDX_MYTEST_TMP 0 3 3584
TABLE PARTITION MYTEST_TMP 0 32 32768
Table MYTEST altered.
TABLE_NAME COM PARTITION_NAME COMPRESS COMPRESS_FOR
------------ --- ---------------- -------- ------------------------------
MYTEST YES P201802 ENABLED QUERY HIGH
TABLE_NAME SUBPARTITION_NAME COMPRESS COMPRESS_FOR
------------- ------------------ -------- ------------------------------
MYTEST SYS_SUBP112447 ENABLED QUERY HIGH
MYTEST SYS_SUBP112448 ENABLED QUERY HIGH
MYTEST SYS_SUBP112449 ENABLED QUERY HIGH
MYTEST SYS_SUBP112450 ENABLED QUERY HIGH
SEGMENT_TYPE SEGMENT_NAME IN_GB IN_MB IN_KB
------------------ ---------------------- ---------- ----------
TABLE SUBPARTITION MYTEST 0 32 32768
INDEX SUBPARTITION UIDX_MYTEST 0 3 3584
TABLE_NAME COM PARTITION_NAME COMPRESS COMPRESS_FOR
----------------- --- ----------------- -------- ------------------------------
MYTEST YES P201802 ENABLED QUERY HIGH
TABLE_NAME SUBPARTITION_NAME COMPRESS COMPRESS_FOR
------------- ------------------ -------- ------------------------------
MYTEST SYS_SUBP112447 ENABLED QUERY HIGH
MYTEST SYS_SUBP112448 ENABLED QUERY HIGH
MYTEST SYS_SUBP112449 ENABLED QUERY HIGH
MYTEST SYS_SUBP112450 ENABLED QUERY HIGH