In addition to Advanced LOB Compression (which was discussed in last month’s blog (see here)), 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. This means that Oracle Database stores one image of the SecureFiles data and replaces the duplicate copies with references to this image. Advanced LOB Deduplication (previously known as SecureFiles LOB Deduplication with Oracle Database 11g) is a feature of Advanced Compression.
Consider an email application where 10 users receive an email with the same 1MB attachment. Without Advanced LOB Deduplication, the database would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application used Advanced LOB Deduplication, it will store the 1MB attachment just once. That is a 90% savings in storage requirements.
Advanced LOB Deduplication eliminates multiple, redundant copies of SecureFiles data and is completely transparent to applications. Oracle automatically detects multiple, identical SecureFiles data and stores only one copy, thereby saving storage space. Deduplication not only simplifies storage management but also results in significantly better performance, especially for copy operations.
Deduplication Insight: Deduplication is done on a per LOB segment basis; duplicates are not detected across multiple LOB segments. While deduplication can be specified at the partition level, duplicate detection does not span across partitions or subpartitions for partitioned SecureFiles columns.
The DEDUPLICATE keyword is used to enable Advanced LOB Deduplication checking for SecureFiles. Oracle uses a secure hash index to detect duplicate SecureFile data and stores a single copy for all identical content. The KEEP_DUPLICATES keyword is used to turn off deduplication and retain duplicate copies of SecureFile data. Deduplication is turned off by default.
This example demonstrates how to create a table with a SECUREFILE LOB column and LOB deduplication enabled.
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE (
For more information (and usage examples) about SecureFiles and LOB compression and deduplication, please see:
Oracle® Database SecureFiles and Large Objects Developer's Guide. (See here)
Oracle SecureFiles Technical White paper. (See here)
We will discuss Flashback Data Archive history table compression in next month’s blog.
The database storage optimization adventure continues!