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

edw1_ora_3937.trc:nrows: 6368 < − Query High, 6368 rows per CU
edw1_ora_5357.trc: NUMP: 05 < − Archive Low, 5 blocks per compression unit
edw1_ora_5357.trc:nrows: 8222 < − Archive Low, 8222 rows per CU
edw1_ora_4219.trc: NUMP: 07 < − Archive High, 7 blocks per compression unit
edw1_ora_4219.trc:nrows: 32759 < − Archive High, 32759 rows per CU
16-4. Determining the Compression Type of a Segment
Problem
You wish to determine whether a table or partition is compressed, decide which type of compression the segment is
using, and identify the compression attributes for individual rows within a table or partition.
Solution
In this recipe, you will learn how to query a table's compression settings and determine the compression type for
individual rows in a table.
To determine which type of compression is used for a table, query the COMPRESSION and COMPRESSED_FOR
columns in DBA_TABLES or DBA_TAB_PARTITIONS . In the following, we are showing the settings for two tables with
different compression configurations:
SQL> select table_name, compression,compress_for
2 from user_tables
3 where table_name in ('MTL_MATERIAL_TRANSACTIONS','MMT_COMPQUERYHIGH');
TABLE_NAME COMPRESSION COMPRESS_FOR
------------------------------ ----------- ------------
MMT_COMPQUERYHIGH ENABLED QUERY HIGH
MTL_MATERIAL_TRANSACTIONS DISABLED
SQL>
The default compression type for MMT_COMPQUERYHIGH is compressed for Query High and MTL_MATERIAL_
TRANSACTIONS is uncompressed. For the compressed table, this means that if rows are inserted via direct path load,
they will be formatted into an HCC compression unit and compressed using the HCC Query High algorithms.
If rows are individually inserted into an HCC segment via conventional inserts, the resulting rows will be loaded
as uncompressed. Use the DBMS_COMPRESSION.GET_COMPRESSION_TYPE function to display the compression type for
individual rows in a table. The example in Listing 16-3 demonstrates querying HCC-compressed rows from one of the
tables in the previous example.
Listing 16-3. lst16-03-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
from "&&owner"."&&table_name"
where &&predicate
/
Enter value for owner: D14
Enter value for table_name: OBJ_COMP1
Enter value for predicate: ROWNUM < 2