Large Objects (LOBs): A Brief History Lesson
first time I encountered an application that required a binary large object
(BLOB) was literally a millennium ago. In 1999, our IT organization at the
American Medical Association (AMA) in Chicago, Illinois was tasked with
upgrading that organization’s seventy-plus mainframe applications into four
client server applications. I led one of several development teams that used
PowerBuilder to develop the next generation of client-server front-end applications.
flagship membership processing application had a requirement to store a
physician’s personal photograph so that when she accessed the AMA’s web site,
her picture would appear next to her personal information. We later expanded
this requirement so that we could store images of a physician practice location
or an organization’s logo. We also developed a mailing list application that
utilized Microsoft Word documents as templates for mailing labels, form
letters, and fax cover sheets so that we could easily generate documents for
distributing membership communication in standardized formats. During the initial
project development cycle we utilized Informix as our RDBMS, and it had the
capability to store JPEGs and Microsoft Word documents as BLOBs.
that time our long-term vision for these images and documents was that they’d
be loaded one time, updated only infrequently, and would be relatively small in
size. And as it turns out, we weren’t the only IT team with this relatively
narrow vision; in fact, these assumptions are reflected in the initial roll-out
of Large Object capabilities in Oracle Database 8i as well:
never grow beyond a size of a few
hundred kilobytes or in extreme cases a megabyte or two. Based on this
assumption, the largest “chunk size” – the maximum amount of space that Oracle
would have to convert from its source file to a LOB at one time – could be kept
relatively small as well, at just 32KB.
LOBs would be loaded
relatively infrequently – usually, only once – and then would be updated very rarely. After all, how often
does a standard mailing label format or form letter change?
Likewise, we anticipated that only one application user session would be
modifying a physician’s picture or a mailing label master document at one time.
Therefore, concurrency and read consistency was not going to be a major
concern, so we didn’t need to worry about the impact of maintaining additional
rollback segments just for LOBs.
Finally, remember that Oracle 8i didn’t yet offer the complete power of
the clustered, multi-instance database to support highly-available, scalable
applications; that only happened when Oracle 9i
introduced Cache Fusion as part of its deployment of Real Application Clusters.
So … What’s Changed?
the risk of overstating the obvious, the footprint of Large Objects has increased
exponentially within our databases over the last decade. It’s being driven by
diverse business needs that span every industry: medical and pharmaceutical
applications that capture and serve up patient images, manufacturing
applications that store components’ designs, and semi-structured data that
business-to-business (B2B) applications use to order products to replenish a
company’s warehouse. However, these newer application requirements have several
things in common:
LOB sources are
much more diverse. These sources include Extended Markup Language (XML) documents, geospatial data,
biometric information, video files, and other semi-structured or non-structured
multiple-user updates are common. As semi-structured data like XML
has proliferated, the need to provide the ability for more than one user to
simultaneously update the same LOB has also exploded.
are gigantic … and getting larger. It’s not uncommon for LOBs to
extend into the gigabyte and even terabyte range, especially for audiovisual
and medical imaging data.
bandwidths have increased dramatically. Likewise, the applications
that consume these LOBs are now able to take advantage of the fiber-optics
broadband lines that telecommunications providers laid down in the late 1990s.
(I have several friends who’ve terminated their cable TV subscriptions because
most of the television shows they want to watch are available as streaming
video from Internet content providers for no additional cost beyond that of their
broadband internet service.)
user content has exploded. The YouTube phenomenon and other web
sites that store self-produced audiovisual files for wide consumption has also
contributed to the explosion of LOB sources.
requirements have increased dramatically. Finally, many of these
LOBs require special security – especially personal medical data that is stored
within Digital Imaging
for Communication of Medical Information (DICOM)
sources – because of federal, state, and international regulations like the Health Insurance Portability and Accountability Act
LOBs, the Next Generation: SecureFiles
result of these new business requirements, the LOB data type has been
redesigned completely in Oracle 11g
Release 1. This new LOB datatype is called a SecureFile,
but unfortunately that’s a bit of a misnomer because security enhancements are
a relatively small subset of SecureFile’s features, as we’ll see throughout this
Performance and Manageability Improvements.
Since the sources of LOBs have increased in size by several orders of magnitude
in the past decade, it was obvious that the methods that load, store, and
return data from LOBs needed complete refurbishment. Oracle 11g doesn’t disappoint in this regard:
Bigger CHUNK sizes. In prior releases, the maximum size of a
“chunk” of LOB data that could be read or written at one time was limited to 32KB.
Internal and external LOB sizes have grown exponentially since Oracle 8i, so this was a serious limitation to
effective LOB I/O. Oracle 11g remedies this by automatically choosing an
size between the database’s standard block size and a maximum of 64MB. Further,
Oracle 11g helps limit the internal fragmentation of SecureFile LOBs
by attempting to store extremely large SecureFile LOB segments on disk so that
they’re physically adjacent. Finally, the ability to utilize variable CHUNK
sizes means that the corresponding UNDO segments generated during updates of
SecureFile LOBs only need to be as large as the CHUNKs and thus contain less
Improved REDO and UNDO Generation.
Before Oracle 11g, the Oracle DBA
had relatively free rein when configuring CHUNK size, as well as the retention
of undo segments for LOBs, and that didn’t always yield optimal results. But
since Oracle 11g will choose automatically
the best method for generating redo and undo for SecureFile LOBs, only two storage-related
parameters are required: RETENTION and MAXSIZE. The RETENTION
directive tells Oracle 11g how to
handle the consistent read data
(stored in UNDO segments) for a SecureFile LOB. Here’s how the setting affects
Table 1-1. RETENTION
keeps old versions
new MAXSIZE storage setting works in concert with the
setting for RETENTION,
and it determines the maximum size of a SecureFile LOB. When RETENTION
is set to MAX,
then the LOB is allowed to grow up to a size of MAXSIZE, after which space can be
reclaimed from UNDO segments; otherwise, the MAXSIZE setting is treated as a
hard limit on the size of the SecureFile LOB’s segment.
that the storage attributes CHUNK, PCTVERSION, FREEPOOLS,
GROUPS are simply ignored for SecureFile LOBs
“Intelligent Compression.” This new
feature allows Oracle 11g to compress and uncompress SecureFile LOB data
automatically and transparently, and this means that disk space for these data
is reduced dramatically. Whenever a query or DML statement retrieves or applies
changes to a SecureFile LOB’s data, Oracle 11g
uncompresses only those data blocks needed for the operation, and it
automatically maintains the required mapping between the uncompressed data
blocks in the Database Buffer Cache and the database blocks on disk. There are two
compression levels: MEDIUM (the default) and HIGH;
will tend to increase latency during SecureFile LOB access, but will also
result in the highest storage compression ratios.
Automatic Deduplication. One of the more
intriguing SecureFile LOB features is the ability for Oracle 11g to automatically locate any LOBs that
store exactly the same data within the same table and column. Once detected,
Oracle 11g will store only one copy
of the LOB data. The advantage here, of course, is dramatically reduced amounts
of disk space are needed to store the same amount of data; further, redo
logging and UNDO is reduced significantly because an update to SecureFile LOB
data doesn’t require retaining duplicate versions of redo log entries and UNDO
segments. Deduplication of SecureFile LOB data is enforceable at either the
table or partition level. Note that deduplication does require the Oracle Advanced Compression option, a
separately-licensed option that’s also required for OLTP table compression and
several other advanced compression features in Oracle 11g.
Transparent Encryption. Every organization
has ethical and legal obligations to protect customer-sensitive
information stored within LOBs. For example, a patient’s medical
records and radiology images must be protected under HIPAA regulations.
However, there’s also enormous amounts of intellectual
property information that must be stored in semi-structured object
forms – for example, molecular formulas and manufacturing blueprints – that is
crucial to any company’s competitive advantage.
LOBs fill this hitherto serious gap in database security because it’s now
possible to encrypt LOB data within an Oracle 11g database. SecureFile LOBs are encrypted using the same Transparent Data Encryption (TDE)
algorithms that were introduced in Oracle 10gR2,
and this means it’s possible to encrypt either any or all SecureFile LOBs
within the same table using the four standard TDE encryption algorithms
(AES128, AES192, AES256 and 3DES168). Finally, note that TDE does require the
Oracle Advanced Security pack, an extra-cost option, to enable encryption of
Wait! What About My “Old” LOBs?
DBA must set the value of the COMPATIBLE initialization
parameter to 220.127.116.11.0
or higher to enable SecureFile LOB features. However, the original LOB datatype
and its capabilities are still preserved for backward compatibility: A
pre-11gR1 LOB is now called a BasicFile.
BasicFile and SecureFile LOBs can even co-exist in the same table. In fact, if
a LOB is not specified as a SecureFile during its creation, then Oracle 11g
simply assumes it to be a BasicFile LOB.
Controlling Selection of SecureFiles for LOB Storage.
The new DB_SECUREFILE
initialization parameter offers another layer of control over how Oracle 11g chooses the type of LOB to create when
neither the BasicFile or SecureFile option is specified:
(Default) Allows SecureFiles to be created
that if a SecureFile LOB storage option is specified during the creation or
modification of a BasicFile LOB – for example, TDE encryption, deduplication,
or compression – Oracle 11g will raise an exception during the operation.
Using SecureFiles: A Practical Example
illustrate how simple and practical it is to create and use SecureFile LOBs, I’ll
construct a sample application that captures trouble ticket information. Along
with each trouble ticket identifier and description, the application needs to
retain a Microsoft Word document that each customer submits when a trouble
ticket is created; optionally, the customer can also provide an appropriate
screen print or other image in JPEG format to help clarify the problem
provided the code to create the initial infrastructure for this application in Listing
First, I’ll create two new tablespaces, BASICFILES
in which all BasicFile and SecureFile LOBs will be stored, respectively.
Next, I’ll create a new schema named TRBTKT
that will own all database objects for this application.
To provide a location from which the Microsoft Word
documents and JPEG images will be stored as external LOBs until they’re loaded
into the database, I’ll create two new directory objects, TRBTKT_DOCS
sample Microsoft Word documents and JPEG files can be downloaded as a single ZIP file. To duplicate this application
infrastructure, you’ll need to create directory folders that correspond to the
ones created above and then copy the documents and images into their respective
that my infrastructure is in place, I’ll construct two tables to demonstrate
how to store the trouble ticket information and related LOBs. As shown in Listing
1.2, both tables are virtually identical; however, table TRBTKT.TICKETS
will utilize BasicFiles to store its LOBs, while, table TRBTKT.SECURE_TICKETS
will use SecureFiles. I’ll be using both tables in this article series to
compare and contrast the different features of BasicFiles and SecureFiles.
construct a new package, TRBTKT.PKG_SECUREFILES, that’s
shown in Listing 1.3.
I’ll use this package to implement all methods for loading LOBs, determining
LOB sizing and space utilization, and displaying rudimentary information from
the character-based LOBs.
I’ll load sample data into table TRBTKT.TICKETS via calls to
in package TRBTKT.PKG_SECUREFILES.
I’ve shown the invocation of this procedure in Listing
1.4. The sample Microsoft Word documents and JPEG files can be
downloaded as a single ZIP file here;
to duplicate my loading technique, you’ll need to create directory folders that
correspond to the ones defined in for directory objects TRBTKT_DOCS
I’ll transfer the data from TRBTKT.TICKETS to TRBTKT.SECURE_TICKETS
using a simple INSERT
INTO … SELECT statement as shown in Listing
the next article in this series, I’ll delve a lot more deeply into the advanced
capabilities of Oracle Database 11g’s
new SecureFile features, including:
efficiently from BasicFile LOBs to SecureFile LOBs
- Determining the relative storage efficiency of SecureFile vs. BasicFile LOBs
Exploring the different compression and deduplication
options for SecureFile LOBs
SecureFile LOBs via Transparent Data
References and Additional Reading
I’m hopeful that I’ve given you a thorough grounding in the technical aspects
of the features I’ve discussed in this article, I’m also sure that there may be
better documentation available since it’s been published. I therefore strongly
suggest that you take a close look at the corresponding Oracle documentation on
these features to obtain crystal-clear understanding before attempting to
implement them in a production environment. Please note that I’ve drawn upon
the following Oracle Database 11g
documentation for the deeper technical details of this article:
B28279-02 Oracle Database 11g New Features Guide
B28320-01 Oracle Database 11g Reference Guide
B28393-03 Oracle Database 11g SecureFiles and Large Objects
B28419-02 Oracle Database 11g PL/SQL Packages and Types
B28424-03 Oracle Database 11g Advanced Application Developer’s