Hybrid Columnar Compression - Oracle Exadata Recipes: A Problem-Solution Approach - page 483

Solution
In this recipe, you will learn how to insert data via conventional INSERT statements into your HCC segments and
identify the compression type on the inserted rows. In the following examples, we will create two empty tables,
HCC-compressed for query high, in two different tablespaces with different default compression attributes.

■ Note Please see recipe 16-2 to learn more about setting default compression attributes for your tablespaces.

Our tablespace's default compression attributes are specified as follows:

SQL> select tablespace_name,
def_tab_compression,
nvl(compress_for,'NONE') compress_for
from dba_tablespaces
where tablespace_name like 'TBS%';

Tablespace CompSetting CompressType
---------------------- ----------------- --------------------
TBS_TEST DISABLED NONE
TBS_QUERYHIGH ENABLED QUERY HIGH

Using these tablespaces, we will create two empty tables, each compressed for query high:

SQL> create table d14.myobj_tab1
tablespace tbs_test compress for query high
as select * from dba_objects where 1=2;
Table created.
SQL> create table d14.myobj_tab2
tablespace tbs_queryhigh compress for query high
as select * from dba_objects where 1=2;
Table created.
SQL>

Now, we will insert rows via conventional insert into these tables and use the script in Listing 16-7 to determine
the compression type of the rows:

SQL> insert into d14.myobj_tab1 select * from dba_objects;
20430 rows created.
SQL> insert into d14.myobj_tab2 select * from dba_objects;
20430 rows created.
SQL> commit;
Commit complete.
SQL>

Listing 16-7. lst16-07-comptype.sql

SQL> select '&&owner' owner, '&&table_name' tabname, rowid myrowid,
decode(dbms_compression.get_compression_type('&&owner','&&table_name',rowid),
1,'No Compression', 2,'Basic/OLTP', 4,'HCC Query High',8,'HCC Query Low',
16,'HCC Archive High', 32,'HCC Archive Low',64,'Block') comptype