Choose a table-compression candidate from the way its data is loaded and changed, then confirm that the database edition, service, licensed options and storage support that method. Compression can reduce storage and reads while adding processor work during loading and changes. Its value depends on the data and workload.
Oracle DBA Lesson 61 Part 1 — Match Compression to the Way Data Changes
How row compression represents the same data
Basic table compression and advanced row compression use a block-local symbol table. Repeated values can be represented by shorter references. The symbol table and the information required to reconstruct the original rows are in the same data block. Applications continue to query the original values.
Three history rows that all contain REGION = NORTH and STATUS = CLOSED can store NORTH and CLOSED in that symbol table and refer to them from each row. The three row identities and logical values remain unchanged.
Compressible repetition, row width, block utilization, and write behavior affect the result. Some data offers little benefit. Smaller storage does not automatically produce an acceptable load or transaction response time.
See table compression for block-local symbols and Basic and Advanced row compression.
Match the method to the write pattern
| Workload | Candidate | Reason and condition |
|---|---|---|
| History populated by large supported direct path loads, with rare later changes | Basic table compression | Compress eligible bulk-loaded data; verify supporting edition/service and actual load path |
| Orders receiving conventional inserts and updates throughout the day | Advanced row compression | Supports conventional DML and bulk loading; confirm Advanced Compression entitlement and measure costs |
| Tiny lookup table | Often retain its current representation | Space and read savings may be too small to justify additional work; verify against the actual workload |
Basic table compression attempts to compress eligible direct path inserts when productive. Examples include an eligible CREATE TABLE AS SELECT or direct path INSERT. Conventional changes do not maintain basic compression for the changed data: newly inserted data and updated values can be uncompressed. An APPEND hint or an intended load mode still needs verification against operation restrictions and actual behavior.
Advanced row compression supports conventional single-row inserts and updates, array inserts, and bulk loads. It uses database-controlled block thresholds. Inserted data and updated values are not necessarily compressed immediately; changed values can initially be uncompressed and be recompressed when the threshold is reached. Unchanged columns may retain their existing compressed representation. Supporting DML does not eliminate the need to test its processor cost and response time.
The clauses identifying these two row-compression methods are:
ROW STORE COMPRESS BASIC
ROW STORE COMPRESS ADVANCEDCOMPRESS alone is equivalent to basic table compression. Older syntax such as COMPRESS FOR OLTP refers to advanced row compression.
See managing table compression for supported writes and Advanced thresholds, and CREATE TABLE for the compression clauses and their restrictions.
Confirm applicability before using compression
Use the current Oracle Database 19c Licensing Information matrix and the terms for the deployment. The check covers the exact release/RU, edition, database service or deployment model, option entitlements, and storage platform.
- Basic Table Compression is available only in supporting editions and services. The 19c matrix does not make it a general Standard Edition 2 feature.
- Advanced Row Compression is an Oracle Advanced Compression feature. The applicable extra-cost option or service entitlement must be confirmed. Cloud service inclusion varies by offering.
- Hybrid Columnar Compression has edition, service, and supported-storage conditions. Support extends beyond Exadata to certain other Oracle storage systems. It is not a general compression mode for an arbitrary filesystem. Additional features such as row-level locking can have further option conditions.
- Index compression concerns index entries. Prefix and key compression, and Advanced Index Compression, have their own applicability and entitlement conditions. A table-compression choice does not settle index-compression rights.
V$OPTION describes installed software options and features. A TRUE value is an installed-capability observation. Usage rights come from the applicable licensing terms and entitlement records. Successful SQL parsing is not an entitlement check.
Hybrid Columnar Compression groups values by column within compression units and is useful for suitable warehouse and archive workloads. Its load, update, and concurrency behavior needs its own evaluation.
See Oracle 19c Licensing Information for feature, edition, service, and storage applicability, V$OPTION for installed capability fields, and table compression for Hybrid Columnar Compression concepts.
Check the configured table method
In SQL*Plus, SHOW USER and SHOW CON_NAME identify the connected user and container. USER_TABLES lists tables owned by that user. COMPRESSION reports whether table compression is enabled. COMPRESS_FOR reports the configured method.
SHOW USER
SHOW CON_NAME
SELECT table_name, compression, compress_for
FROM user_tables
WHERE table_name IN ('HISTORY', 'ORDERS');For these two nonpartitioned tables, HISTORY is enabled for basic compression and ORDERS is uncompressed. The blank COMPRESS_FOR cell is NULL.
| TABLE_NAME | COMPRESSION | COMPRESS_FOR |
|---|---|---|
| HISTORY | ENABLED | BASIC |
| ORDERS | DISABLED |
These metadata fields do not measure a savings ratio or determine the format of every existing row. A table can contain data written under different settings and load paths. A missing row means that name is not a table owned by the connected user.
For a partitioned table, read the partition settings:
SELECT table_name, partition_name, compression, compress_for
FROM user_tab_partitions
WHERE table_name = 'HISTORY'
ORDER BY partition_name;USER_TABLES.COMPRESSION is NULL for a partitioned table. A partition's setting takes precedence for that partition. Read subpartition settings when the table has subpartitions. A parent-table summary is not proof of a uniform physical representation.
See USER_TABLES and ALL_TABLES for COMPRESSION, COMPRESS_FOR, and table scope.
Changing the attribute does not rewrite existing data
Changing a compression attribute governs subsequent eligible writes. Existing blocks keep their representation until an operation rewrites them. A table move or online redefinition can reorganize existing data, subject to its restrictions and operating conditions.
For existing data, plan available space, locking and concurrent access, dependent indexes, statistics, recovery, and a tested reversal/reset approach. These operations change physical organization and may affect row identifiers and index usability, depending on the operation and release.
These statements create copies. They do not change the source tables:
CREATE TABLE history_basic ROW STORE COMPRESS BASIC
AS SELECT * FROM history;
CREATE TABLE orders_advanced ROW STORE COMPRESS ADVANCED
AS SELECT * FROM orders;Selecting matching rows is only one part of a representative comparison. Data correctness, allocated bytes, reads, load time, processor work, and update behavior all need evaluation. A small starter table cannot establish a production benefit.
See ALTER TABLE for attribute changes and move behavior, and managing table compression for changing existing data.
Recap
Choose the method from the write pattern. Basic table compression and advanced row compression keep a block-local symbol table in the same data block as the rows, and applications still read the original values. Basic table compression targets eligible direct path loads, such as CREATE TABLE AS SELECT or direct path INSERT, when compression is productive. Conventional changes can leave newly inserted data and updated values uncompressed. Advanced row compression supports conventional single-row inserts and updates, array inserts, and bulk loads, and recompresses a block when a database-controlled threshold is reached. Changed values can start uncompressed. Unchanged columns may stay compressed. A tiny lookup table may not repay the extra processor work.
ROW STORE COMPRESS BASIC and ROW STORE COMPRESS ADVANCED name the two methods. COMPRESS alone means basic table compression, and COMPRESS FOR OLTP is older syntax for advanced row compression. Basic Table Compression is not a general Standard Edition 2 feature in the 19c matrix. Advanced Row Compression needs Oracle Advanced Compression entitlement, and cloud inclusion varies by service. Hybrid Columnar Compression needs a supporting edition, service, and storage system. V$OPTION equal to TRUE reports installed capability, and successful parsing is not an entitlement check. Index compression has its own conditions. For a nonpartitioned table, USER_TABLES.COMPRESSION and COMPRESS_FOR report the configured method, not a savings ratio or the format of every existing row. For a partitioned table, USER_TABLES.COMPRESSION is NULL and each partition setting applies to that partition. Changing the attribute governs later eligible writes. Existing blocks stay as they are until a table move or online redefinition rewrites them. CREATE TABLE ... AS SELECT with a compression clause builds a copy and leaves the source table unchanged.
No comments:
Post a Comment