Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

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