Compressing securefile columns have different syntax, syntax that specifiles compression within the LOB clause. For more details see Document 1459216.1. The strategies I used in the original article were: 1. Basic compression is a free option with the Enterprise Edition version of the database. We’ll start by creating a table with 50,000 rows selected from view all_objects, and check to see if different strategies produce different results. If a table contains LOB columns then it is possible to compress table only  without compressing the LOB data. NOTE: Currently Advanced Compression cannot be disabled. As it is important to share common terminology for this topic an excerpt from the manual is provided below for clarity. The algorithm works by eliminating duplicate values within a database block, even across multiple columns. Answered by: Connor McDonald - Last updated: August 16, 2017 - 11:58 am UTC. If it is, please let us know via a Review. OLTP Table Compression reduces the associated compression overhead of write operations making it suitable for transactional or OLTP environments as well. COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. ALTER TABLE sales MERGE PARTITIONS sales_q1_1998, sales_q2_1998 INTO PARTITION sales_1_1998 TABLESPACE ts_arch_1_1998 COMPRESS FOR OLTP UPDATE INDEXES; For more details and examples for partition management operations, refer to Partition Administration . ” Oracle’s OLTP Table Compression uses a unique compression algorithm specifically designed to work with OLTP application. When a table is set to use OLTP compression and rows are inserted into a new block in that table they are inserted uncompressed, just as with a non-compressed table. Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so. OLTP Table Compression is a part of the Oracle Advanced Compression option, which requires a license in addition to the Enterprise Edition. The Oracle11g Advanced Compression Option includes OLTP Compression, SecureFiles Deduplication and Compression (LOBs), Recovery Manager Compression, Data Pump Compression, and DataGuard redo compression. This document is focused solely on OLTP Compression which is one of several types of heap segment compression (HSC), the other features of the Advanced Compression Option as listed above are not covered here. In the above explanation can you mention why was the blocks space not got released even though the table is compressed what could be done to release the blocks, Is this answer out of date? This form of compression is recommended for OLTP environments. This document describes the 11g new feature OLTP Compression and provides usage guidelines that help to maximize compression ratio and performance. ALTER TABLE DV_XXXXXX_HST MOVE COMPRESS FOR OLTP; checking the size of the table after compression. And of course, keep up to date with AskTOM via the official twitter account. Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database! * When you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compression. My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. Connor and Chris don't just spend all day on AskTOM. Expertise through exercise! Note that Hybrid Columnar Compression has specific requirements for underlying storage including Exadata, ZFS, or Pillar. It should be noted that Basic Table Compression is a base feature of Oracle Database 11g Enterprise Edition (EE). As the name implies, this option enables compression for all operations, including regular DML statements. Oracle Database compresses data during all DML operations on the table. Our very first test of basic compression involved defining a table with compression, and seeing what the data looked like on an initial load. Compress = compress data *only* during direct mode operations (sqlldr, ctas, insert-append, alter table move). Compressed blocks contain a structure called a symbol table that maintains compression metadata. All subsequent (aka normal) DML will not create compressed data. OLTP Table Compression, therefore, extends the benefits of compression to all application workloads. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. select segment_name, segment_type, bytes/1024/1024 MB from user_segments where segment_type='TABLE' and segment_name='DV_XXXXXX_HST'; --- 4341 (Size in MB before COMPRESSION) Then we compressed the table for OLTP using. OLTP compression differs from basic compression found in prior versions as it allows compression during DML operations and not just during direct path loads. In earlier releases, OLTP table compression was enabled using COMPRESS FOR ALL OPERATIONS. It is designed for use with direct path loads only. Basic compression comes with oracle 11g Enterprise Edition, To make table as OLTP compressed its again extra cost option with … Basic and Advanced compression are installed by default without the need of explicitly selecting the component for install during the installation of the Oracle Database Server, or creation of the database. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. From a licensing point of view, the Feature Usage is the deciding factor for "Advanced Compression". Create table as select from all_obje… Enhancement has been logged to request the possibility to disable/un-install the option. Basic compression is not designed for OLTP operations. We’ll do the same again, but use the option “compress for OLTP” (or “compress for all operations” for those using a slightly older version of Oracle). * When you enable table compression by specifying either COMPRESS or COMPRESS BASIC, you enable basic table compression. You will get no compression benefits from conventional path inserts, updates or deletes. See more about this at, http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB45960, Compression Effectiveness and Performance, TEST #2: Query performing a full table scan, TEST #3: Query repeated 1,000,000 times reading single rows by rowid in random order, TEST #4: Update all rows setting a non-redundant column to the same value, TEST #5: Update all rows setting a redundant column to a new value not present prior to the update. It is sufficient to document that you are not using this feature. Classes, workouts and quizzes on Oracle Database technologies. You can also catch regular content via Connor's blog and Chris's blog. The original import utility (imp) does not support direct-path INSERT, and therefore cannot import data in a compressed format.