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

from "&&owner"."&&table_name"
where &&predicate
/
Enter value for owner: D14
Enter value for table_name: MYOBJ_TAB1
Enter value for predicate: ROWNUM < 2
Owner Table RowId CompType
--------------- ---------------------- -------------------- --------------------
D14 MYOBJ_TAB1 AAAG2mAAAAABcyDAAA No Compression
/
Enter value for owner: D14
Enter value for table_name: MYOBJ_TAB2
Enter value for predicate: ROWNUM < 2
Owner Table RowId CompType
--------------- ---------------------- -------------------- --------------------
D14 MYOBJ_TAB2 AAAG2nAAAAAAACDAAA No Compression
How It Works
Oracle allows you to use conventional insert or load mechanisms to populated HCC tables or partitions, but it is
important to understand the compression impact when doing so. If you have applications or data load strategies that
cannot be tailored to perform direct path inserts, your code will function without change and without generating error
messages, but the data will be inserted as uncompressed to your tables and/or partitions.
Using DBMS_COMPRESSION.GET_COMPRESSION_TYPE for individual row and comparing against DBA_TABLES.
COMPRESS_FOR and/or DBA_TAB_PARTITIONS.COMPRESS_FOR is a good way to determine whether you have HCC
segments that are being populated via conventional inserts or loads.
If your application inserts data via conventional inserts or loads to your HCC segments, over time your
compression ratios and expected storage and I/O savings will be diminished. If you deem it worthwhile, you can
correct this behavior by rebuilding your tables or partitions with the ALTER ... MOVE DDL statement.
16-8. DML and HCC
Problem
You have learned through Oracle's documentation or other sources that performing DML on HCC tables is
discouraged for performance reasons, and you wish to measure and identify situations in which DML has taken place
on your HCC segments.
Solution
In this recipe, you will learn about various aspects of DML and HCC tables, including how to determine whether a row
in an HCC table has been updated, how to determine the percentage of a segment's rows that have been updated, and
how to measure concurrency and locking issues that may arise due to DML on HCC segments. We will focus primarily
on the impact of UPDATE statements in this section, as INSERT mechanics were presented in Recipes 16-6 and 16-7.