Search My Oracle Blog

Custom Search

28 February, 2016

Compression -- 1b : (more on) BASIC Table Compression

In the previous blog, I demonstrated creating an empty table with BASIC Compression and then populating it.

What if you have a pre-existing table with data that you want to compress ?

Here I start with a table that already has rows but doesn't have compression configured.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name = 'REGULAR_1';

TABLESPACE_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
HEMANT          TABLE        49

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
  2  from user_tables
  3  where table_name = 'REGULAR_1';

  PCT_FREE     BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
 10  6224   364496       48.625

PDB1@ORCL>


I then proceed to rebuild it as a COMPRESSed table.

PDB1@ORCL> alter table regular_1 move compress;

Table altered.

PDB1@ORCL> exec dbms_stats.gather_table_stats('','REGULAR_1');

PL/SQL procedure successfully completed.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name = 'REGULAR_1';

TABLESPACE_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
HEMANT          TABLE        12

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
  2  from user_tables
  3  where table_name = 'REGULAR_1';

  PCT_FREE     BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
  0  1448   364496      11.3125

PDB1@ORCL> 


Note how not only did the table size shrink to less than 12MB, the PCT_FREE also got  reset to 0 !
.
.
.

2 comments:

ROHIT SHARMA said...

How would it impact performance ? and Does Plan is also expected to be changed post compression?

Hemant K Chitale said...

Rohit,
A Full Table Scan has fewer blocks to read. That reduces the I/O cost (provided the statistics are correctly updated). There may be some CPU cycles for the "uncompression" but the gain in I/O would outweigh that.
On the other hand, if you are doing indexed lookups, it makes no difference.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016