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

16-6. Direct Path Inserts into HCC Segments
Problem
You wish to know how to insert rows into your HCC tables in order to ensure that they are compressed according to
your desired compression type.
Solution
In order to populate rows into HCC segments, you need to load or insert via direct path inserts or loads. There are
a number of ways to load data into Oracle tables or partitions and, in this recipe, you will learn how to use a few of
these techniques. Additionally, you will learn how to use DBMS_COMPRESSION.GET_COMPRESSION_TYPE to determine
the compression type for the inserted rows. The sections in this recipe will be based on an example in which we use
SYS.OBJ$ as the source and methods introduced in Recipe 16-4 (see Listing 16-3) to determine the compression type
of the loaded rows.
Create Table as Select
In the following, we will create a sample table using SYS.OBJ$ as our source:
SQL> create table myobj1
nologging compress for query high
as select * from sys.obj$;
Table created.
SQL>
Using Listing 16-6, we can measure the compression type for one of the rows in the table.
Listing 16-6. lst16-06-comptype.sql
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
from "&&owner"."&&table_name"
where &&predicate
/
Enter value for owner: SYS
Enter value for table_name: MYOBJ1
Enter value for predicate: ROWNUM < 2
Owner Table RowId CompType
--------------- ------------------------------ -------------------- --------------------
SYS MYOBJ1 AAM6zZABZAAAeocAAA HCC Query High
SQL>