Recent Blog Series on RMAN

These have been my recent blog posts on RMAN

1. 1  Backup Job Details  (Jun-15)

2. 2.  ArchiveLog Deletion Policy  (Jun-15)

3. 3.  The DB_UNIQUE_NAME in Backups to the FRA  (Jun-15)

4.  4.  Recovering from an Incomplete Restore  (Jul-15)

5.  4b. Recovering from an Incomplete Restore with OMF Files  (Jul-15)

6.  5.  Useful Keywords and SubClauses  (Jul-15)

7.  5b.  More Useful Keywords and SubClauses  (Jul-15)

8.  5c.  Some More Useful Keywords and SubClauses  (Jul-15)

9.  6.  Retention Policy and CONTROL_FILE_RECORD_KEEP_TIME  (Aug-15)

10.  7.  Recovery through RESETLOGS -- How are the ArchiveLogs Identified ?  (Aug-15)

11.  8.  Using a Recovery Catalog Schema  (Aug-15)

12.  9.  Querying the RMAN Views / Catalog  (Sep-15)

13.  10. VALIDATE  (Sep-15)

14.  Unused Block Compression and Null Block Compression  (Feb-16)


(This list does not include RMAN Blog Posts made before June 2015)
,
,
,

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

Compression -- 1 : BASIC Table Compression

Now, starting a new series of blog posts on Compression.

Beginning with  BASIC Table Compression.

Basic Table Compression was introduced in 9i Release 2 (9.2.0).  It is free with the Enterprise Edition.  Basic Compression works only with Direct Path (Bulk Load) INSERTs.  It does not apply to "normal" INSERT and UPDATE operations.
Basic Compression is actually "de-duplication".  It identifies values that are repeated within the same database block and replaces them with tokens mapped to the list of values.   Note the section that I have underlined.  De-duplication does not span database blocks.

Here is a first demo of Basic Compression using INSERT /*+ APPEND */  (for Direct Path Insert).

I start with a table that is a multiplied copy of DBA_OBJECTS, named as SOURCE_DATA.

PDB1@ORCL> show user
USER is "HEMANT"
PDB1@ORCL> select count(*) from source_data;

  COUNT(*)
----------
    364496

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

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

PDB1@ORCL> 

I then create table to hold compressed data.  Note (let me repeat again : Basic Compression works only with DIRECT PATH INSERT).

PDB1@ORCL> create table compressed_1 tablespace hemant compress as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_1
  2  select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> col segment_name format a30
PDB1@ORCL> select segment_name, segment_type, bytes/1048576
  2  from user_Segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        12
SOURCE_DATA         TABLE        49

PDB1@ORCL> 

So, a 49MB table is compressed down to 12MB.  What if I UPDATE these rows ?

PDB1@ORCL> update compressed_1
  2  set owner = owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

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

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        22
SOURCE_DATA         TABLE        49

PDB1@ORCL> 

Updating only a single column without changing the length of the data in that column has increased the size of the table.

Let me continue the UPDATE experiment further, without increasing the length of data in any columns.

PDB1@ORCL> update compressed_1
  2  set owner = owner, object_name = object_name, subobject_name = subobject_name, object_id = object_id,
  3  data_object_id = data_object_id, object_type = object_type, created = created
  4  /  

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

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

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        22
SOURCE_DATA         TABLE        49

PDB1@ORCL> 

No further degradation ? (unless my UPDATE expands the size of data in one or more column(s)).

Note a difference between the two tables :

PDB1@ORCL> select table_name, compression, compress_for, pct_free
  2  from user_tables;

TABLE_NAME         COMPRESS COMPRESS_FOR    PCT_FREE
------------------------------ -------- ------------------------------ ----------
COMPRESSED_1         ENABLED BASIC     0
SOURCE_DATA         DISABLED            10

PDB1@ORCL> 

The Compressed table is created with PCT_FREE=10.  PCT_FREE=0  (Note that this can also have an impact when you issue UPDATEs subsequently, UPDATEs that increase the size of data in one or more columns).

UPDATE : In 12c the syntax for BASIC Compression is "ROW STORE COMPRESS BASIC". Simply specifying "COMPRESS" or "COMPRESS BASIC" is supported for backward compatibility.

.
.
.