Advanced LOB Compression (previously known as SecureFiles LOB Compression with Oracle Database 11g) is a feature of Advanced Compression that can help reduce the storage requirements of SecureFiles data.
If you are not familiar with SecureFiles, SecureFiles offers a ‘best-of-both-worlds’ architecture for storing unstructured content such as documents, images, spreadsheets and XML files and is specifically engineered to deliver high performance for file data equal to or better than traditional file systems, while retaining the advantages of Oracle Database. SecureFiles is a superset of the ANSI standard LOB data type and offers easy migration from existing BasicFiles LOBs, the precursor to SecureFiles. With SecureFiles, organizations can manage all relational data and associated file data with Oracle Database using a single security/audit model, a unified backup & recovery process and perform seamless retrievals across all information.
Advanced LOB Compression utilizes industry standard compression algorithms to minimize the storage requirements of SecureFiles data. With Advanced LOB Compression, files such as documents or XML files often achieve a 2x to 3x compression ratio. Compression not only results in savings in storage but can also improve performance by reducing I/O, buffer cache requirements, redo generation and encryption overhead. Random access reads and writes to compressed SecureFiles are achieved without the need to decompress the entire file. Only the sub portion of the compressed file, corresponding to the logical offset being read or written, needs to be decompressed, thus saving CPU and I/O. Advanced LOB Compression automatically avoids compressing data that would not benefit from compression, such as a document that was compressed via a 3rd party tool before being inserted into the database as a SecureFiles file.
There are three levels of Advanced LOB Compression: LOW, MEDIUM, and HIGH. By default, Advanced LOB Compression uses the MEDIUM level, which typically provides good compression with a modest CPU overhead of 3%-5%. Advanced LOB Compression LOW is optimized for high performance. Advanced LOB Compression LOW maintains about 80% of the compression achieved through MEDIUM, while utilizing typically 3x less CPU. The LOW compression option uses an extremely lightweight compression algorithm that removes the majority of the CPU cost that is typical with file compression. Compressed SecureFiles LOBs at the LOW level generally provides a very efficient choice for SecureFiles LOB storage. SecureFiles LOBs compressed at LOW generally consume less CPU time and less storage than BasicFiles LOBs, and typically help the application run faster because of a reduction in disk I/O. Advanced LOB Compression HIGH achieves the highest storage savings but incurs the most CPU overhead. While the LOW, MEDIUM, and HIGH options provide varying degrees of compression, remember that the higher the compression, the higher the latency incurred. The HIGH setting incurs more work, but compresses the data better.
The COMPRESS keyword is used to enable Advanced LOB Compression. Advanced LOB Compression is separate from table (Advanced Row) or index (Advanced Index/Prefix) compression. Setting table or index compression does not affect Advanced LOB Compression or vice versa.
These examples demonstrate how to use CREATE TABLE statements for specific compression scenarios.
Creating a SecureFiles LOB Column with LOW Compression
CREATE TABLE t1 (a CLOB) LOB(a) STORE AS SECUREFILE( COMPRESS LOW CACHE NOLOGGING );
Creating a SecureFiles LOB Column with MEDIUM (default) Compression
CREATE TABLE t1 ( a CLOB) LOB(a) STORE AS SECUREFILE ( COMPRESS CACHE NOLOGGING );
Creating a SecureFiles LOB Column with HIGH Compression
CREATE TABLE t1 ( a CLOB) LOB(a) STORE AS SECUREFILE ( COMPRESS HIGH CACHE );
For more information (and usage examples) about SecureFiles and LOB compression, please see:
Oracle® Database SecureFiles and Large Objects Developer's Guide. (See here)
Oracle SecureFiles Technical White Paper. (See here)
In addition to Advanced LOB Compression, Advanced Compression provides another feature, Advanced LOB Deduplication, to help reduce the storage requirements of SecureFiles data. Advanced LOB Deduplication eliminates duplicate copies of SecureFiles data -- Oracle Database stores one image of the SecureFiles data and replaces the duplicate copies with references to this image.
We will discuss Advanced LOB Deduplication in next month’s blog.
The database storage optimization adventure continues!