Using data compression for the workspace in Oracle (Bathymetry Solution)

By using table compression for bulk load operations, you can compress data when performing bulk load using operations such as direct loads or Create Table As Select (CTAS). Compression is available for regular data manipulation operations such as INSERT, UPDATE, and DELETE. Compression can be used for all kinds of workloads such as online transaction processing (OLTP) or data warehousing. Your write performance does not degrade, and your read performance or queries improve. This is because unlike desktop-based compression techniques where you have to wait for data to be uncompressed, Oracle technology reads the compressed data (fewer fetches needed) directly and does not require any uncompress operation.

Compression technology is completely application transparent. This means you can use this technology with any application, such as ArcGIS and Esri Production Mapping.

Using table compression requires an Oracle database that is compatible with 11.1 or later. The following is the syntax for the compress keyword.

COMPRESS [FOR {ALL | DIRECT_LOAD} OPERATIONS]

FOR DIRECT_LOAD is the default; it refers to bulk load operations from prior releases. FOR ALL OPERATIONS is for OLTP and direct loads.

The following are examples of the compress keyword.

--Enable compression for new tables:
CREATE TABLE t1 COMPRESS FOR ALL OPERATIONS;

--Enable compression on existing table:
ALTER TABLE t2 COMPRESS FOR ALL OPERATIONS;
--Does not trigger compression on existing rows

To use the new compression algorithm, you must flag your table with the COMPRESS FOR ALL OPERATIONS clause. You can do so at table creation, or after creation. If you use the COMPRESS clause without specifying any FOR option, or if you use the COMPRESS FOR DIRECT_LOAD OPERATIONS clause, you will use an old compression mechanism that was available in earlier releases.

You can also enable compression at the partition or tablespace level. For example, you can use the DEFAULT storage clause of the CREATE TABLESPACE command to optionally specify a COMPRESS FOR clause.

Remarque :

You can view compression flags for your tables using the COMPRESS and COMPRESS_FOR columns in views such as DBA_TABLES and DBA_TAB_PARTITIONS.

Compression and TDE

If you are using TDE tablespace encryption, you will receive the full benefit of compression (standard and advanced compression, as well as Exadata Hybrid Columnar Compression [EHCC]) because compression is applied before the data blocks are encrypted. Using TDE column encryption will give you the full benefit of compression only on table columns that are not encrypted. Individual table columns that are encrypted using TDE column encryption will have a much lower level of compression because the encryption takes place in the SQL layer before the advanced compression process.

For more information, see Oracle 11g: Transparent Data Encryption (TDE) Frequently Asked Questions.