Tom, I need help.
-If I create a table with COMPRESS BASIC on the partitions and then I insert data from another table does not decrease the size.
-Now if I create a table from another with a:
CREATE TABLE AS SELECT * FROM TABLE_2
And COMPRESS BASIC partitions if the size of the table decreases.
What am I doing wrong?
Thank you.
Compress basic works with *direct* mode operations only. eg lets load 100 megabytes of raw data into an empty table - first with standard insert, and then with a direct-mode insert
SQL> create table t ( x char(1000) ) compress basic;
Table created.
SQL>
SQL> insert into t
2 select 'x'
3 from dual connect by level <= 100000;
100000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select bytes/1024/1024 meg from user_segments
2 where segment_name = 'T';
MEG
----------
104
1 row selected.
SQL>
SQL> truncate table t drop storage;
Table truncated.
SQL>
SQL>
SQL> insert /*+ append */ into t
2 select 'x'
3 from dual connect by level <= 100000;
100000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select bytes/1024/1024 meg from user_segments
2 where segment_name = 'T';
MEG
----------
2
create-table-as-select is a direct mode operation hence the difference you are observing.