Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mac.

Asked: April 18, 2017 - 2:26 pm UTC

Last updated: April 19, 2017 - 1:03 am UTC

Version: 12c

Viewed 1000+ times

You Asked

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.

and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions