Now, that we have set up all the prerequirements for hybrid columnar compression, it’s time to actually do something with it.

4.3 Use Hybrid Columnar Compression

Log into your database as sysdba

[oracle@localhost ~]$ sqlplus / as sysdba


Create a tablespace inside the nfs share:

SQL> create tablespace hcc datafile '/home/oracle/hcc/hcc.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.


Create a second tablespace inside the default location, where hcc is not enabled:

SQL> create tablespace nohcc datafile '/home/oracle/app/oracle/oradata/orcl/nohcc.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.


create a compressed table:

SQL> create table hcc_enabled compress for archive tablespace hcc as select * from dba_objects;
table created


create an uncompressed table containing the same data:

SQL> create table hcc_disabled tablespace nohcc as select * from dba_objects;
table created


Verify, that it was working:

SQL> select table_name, compression, compress_for from dba_tables where table_name like 'HCC%';

TABLE_NAME		       COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
HCC_DISABLED		       DISABLED
HCC_ENABLED		       ENABLED	ARCHIVE LOW

And now, let's see, what all the effort was for:

SQL> select sum(blocks), segment_name from dba_extents where segment_name like 'HCC%' group by segment_name;

SUM(BLOCKS) SEGMENT_NAME
----------- --------------------
       1152 HCC_DISABLED
	 64 HCC_ENABLED

SQL> select sum(bytes), segment_name from dba_extents where segment_name like 'HCC%' group by segment_name;

SUM(BYTES) SEGMENT_NAME
---------- --------------------
   9437184 HCC_DISABLED
    524288 HCC_ENABLED


As you can see, the compressed tables consumes far less space on your hard drive.

Another quick note:
Please keep in mind that when you start your database, oracle checks the availability of all datafiles defined. So from now on you have to make sure, that the zfsapp is running and accessible, before you can start the database.

<-- back to the beginning | go to next lab -->

Leave a Reply

Your email address will not be published. Required fields are marked *