Hello everyone,
I looked for documentation and google it but could not find anything. I have database on an Exadata :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I am compressing old partitions with hybrid columnar compression by using ARCHIVE LOW. So I run this command:
ALTER TABLE MYTABLE MOVE PARTITION P COMPRESS FOR ARCHIVE LOW;
Now I just create a new table by using this partition data like this:
CREATE TABLE TMP COMPRESS FOR ARCHIVE LOW AS SELECT * FROM MYTABLE PARTITION (P);
uncompressed partition data size is 3.6GB, after move partition command partition size is 1GB but when I do it CTAS (create table as select) new table size is 250MB. there is a very huge difference.
Actually I realized this on a much more bigger partition, that partition has 14GB data and after ARCHIVE HIGH compression, partition size is 10GB. this made me suspicious and want to try CTAS and new table size is 1.6GB.
Is there anyone know why there is a huge different between CTAS and move partition compress.
thanks.
PROBLEM IDENTIFIED: hello again I want to explain what happened after many people try to help me here. On my table there is a CLOB field and I am holding lob data as securefile compress high. So when a CLOB data store in the row HCC is not compressing it and leave that row uncompressed but out of row LOB segments are very small because I used COMPRESS HIGH. so if I use ctas then I leave lobs as default (basicfile nocompress) and because of that, HCC compress every row and table become so small but LOB segment become too big because I did not use compress high for lob. it would be nice if I can only compress lob segment which is out of row, for now it is not possible.. thanks to everyone.
Message was edited by: Mustafa KALAYCI