in Oracle

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 !
.
.
.

  • Related Content by Tag