Synopsis.
Oracle Database 11g’s new SecureFiles storage capabilities extend the
flexibility and capacity of Large Objects (LOBs). This article – the next in
this three-part series – explores how to migrate most efficiently from BasicFiles
to SecureFiles storage, how to measure the relative storage efficiency of SecureFile vs. BasicFile LOBs,
and how to utilize the different compression and deduplication options for
SecureFile LOBs.
At
the end of the prior
article in this series, I demonstrated the most straightforward method for
transforming BasicFile LOBs into SecureFile LOBs. I created a new table (TRBTKT.SECURE_TICKETS)
that redefines the structure of the original table (TRBTKT.TICKETS) that maps the
original BasicFile LOBs to SecureFile LOB counterparts, and then issued a
simple INSERT
INTO … SELECT FROM DML statement to transfer the contents of
the original table into the new table.
In a
relatively simple application environment, this loading method is sufficient.
However, today LOBs are being used extensively within data warehousing and
business intelligence applications, especially for storing complex business
transactions within XML documents and for capturing medical images for hospital
information systems. These systems often take advantage of Oracle’s excellent partitioning
features to organize objects for extremely efficient retrieval.
To
illustrate this, I’ve recreated both of my example tables using the code shown
in Listing 2.1.
In this new incarnation, I’ve used list
partitioning to partition both tables on the status of the trouble
ticket entry. Then in Listing 2.2,
I’ve reloaded table TRBTKT.TICKETS with significantly
more data.
Migrating Efficiently from BasicFiles to SecureFiles
Now
that I’ve deployed partitioning features to better organize these tables for
efficient use in a data warehousing environment, I’ve also got to consider how
to transform the LOBs from BasicFiles format quickly and efficiently into their
corresponding SecureFiles counterparts. Fortunately, Oracle 11g offers two such
methods: partition exchange and online redefinition.
Partition Exchange. The Partition Exchange Load (PEL) method –
sometimes called partition roll-on
– involves constructing a non-partitioned table whose layout and contents match
exactly to the desired partition of the target partitioned table. Once the
source table has been populated, then that table is simply exchanged with the target partition in the
already-partitioned table. This approach does offer some advantages; namely,
the source table’s local indexes are maintained during the exchange, it can be
performed in parallel, and this process can be repeated several times to
populate individual partitions over shorter maintenance periods. The primary
disadvantage is that the partition that’s receiving the data must be offline
while the exchange occurs.
Online Redefinition. Oracle recommends using
the DBMS_REDEFINITION
package to perform the online redefinition
of source and target tables because it accomplishes the same goal as the
partition exchange method, yet there’s no requirement to take offline the
target of the redefinition process. Online redefinition can be performed at the
table or partition level, and thus it can also be done in parallel for multiple
partitions. My favorite feature of online redefinition is that after the
process has been completed successfully, the source and target objects will
actually switch definitions
within the database’s data dictionary. This means that any referencing PL/SQL
objects don’t need to be revised to point to the new object’s name.
Listing
2.3 shows how to perform online redefinition using tables
TRBTKT.TICKETS
and TRBTKT.SECURE_TICKETS
as the source and target objects, respectively. One final note on translating
BasicFile LOBs to their counterpart SecureFile LOBs: once conversion has been
completed, that LOB cannot be downgraded to
a BasicFile LOB.
Managing SecureFiles Metadata
While
these new SecureFile features certainly do extend LOB capabilities, it also
means that DBAs need to pay attention to several additional attributes beyond
those of the traditional BasicFile LOBs. Fortunately, Oracle 11g provides several methods to keep track
of BasicFile and SecureFile LOB metadata.
Data Dictionary Views. Oracle 11g also upgraded several data dictionary
views to provide additional information about SecureFiles, and a list of these
modified views is provided below:
|
Table 2-1. Accessing
SecureFiles Metadata Via Data Dictionary Views
|
|
Data Dictionary
View
|
Usage
|
|
DBA_SEGMENTS
|
Shows
all segments in the database,
and includes a new column, SEGMENT_SUBTYPE,
that describes the LOB segment type
|
|
DBA_LOBS
|
Lists
all LOBs in the database, and
now includes metadata about compression, encryption, and deduplication
|
|
DBA_LOB_PARTITIONS
|
Describes
all LOB partitions in the database, and now
includes metadata about compression, encryption, and deduplication
|
|
DBA_PART_LOBS
|
Shows
table-level metadata for all partitioned LOBs in the database
|
Listing
2.4 shows several SQL*Plus queries that utilize these
data dictionary views for reporting on specific SecureFiles metadata, and Report 2.1
shows the resulting output from these queries against the two example tables in
the TRBTKT
schema.
DBMS_SPACE. Another venerable
Oracle-supplied package, DBMS_SPACE’s SPACE_USAGE procedure, has been
overloaded to provide space utilization information about both BasicFile and
SecureFile LOBs. I’ve included both methods in two procedures, CALC_SPACE_BASICFILES
and CALC_SPACE_SECUREFILES,
in package TRBTKT.PKG_SECUREFILES.
However, this new incarnation of SPACE_USAGE will only work
against a tablespace that uses Automatic
Segment Space Management (ASSM); further, it doesn’t consider any
BasicFile LOB chunks as utilized space. In Listing 2.5,
I’ve provided an example of invoking these procedures against tables TRBTKT.TICKETS and TRBTKT.SECURE_TICKETS,
respectively, and the corresponding output returned.
Changing SecureFiles Attributes
When
I recreated table TRBTKT.SECURE_TICKETS in Listing 2.1, you may have noticed that I
also employed different levels of compression
and deduplication for each of the
LOB columns within their different partitions. It’s important to note that I
could have used an appropriate version of the ALTER TABLE statement to
accomplish these revisions as well. Even better, I can apply changes to
SecureFile LOBs within each LOB’s individual partition
if so desired.
The
code in Listing 2.6
shows four examples of changing SecureFile LOB attributes in the TRBTKT.TICKETS
table. (Recall that this table has now exchanged definitions with table TRBTKT.SECURE_TICKETS after
the DBMS_REDEFINITION
operations were implemented in Listing 2.3.)
I’ve also created a report from data dictionary view DBA_PART_LOBS to show the before
and after effects of modifying these LOB attributes; the resulting output is
shown in Report 2.2.
Finally,
it’s important to remember that any modifications to LOB attributes will only
affect newly-created or modified LOBs. For example, changing the compression
method to NOCOMPRESS instead of its current setting of COMPRESS
for column TRBTKT.SECURE_TICKETS.SCRNIMG
will not change the compression factor for any existing LOB entries.
Next Steps
In
the final article in this series, I’ll look at the advanced capabilities of
Oracle Database 11g’s new
SecureFile features, including:
-
Encrypting
SecureFile LOBs via Transparent Data
Encryption
-
Encrypting an entire tablespace using Transparent Data Encryption
-
Storing images in Digital
Imaging for Medical Communication (DICOM) format within SecureFile
LOBs
-
Utilizing the new Shared
I/O Pool memory component for faster LOB I/O processing
References and
Additional Reading
While
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
Developer’s Guide
B28419-02 Oracle Database 11g PL/SQL Packages and Types
Reference
B28424-03 Oracle Database 11g Advanced Application Developer’s
Guide
B32024-01 Oracle Database 11g VLDB and Partitioning Guide
Also,
the following MetaLink documentation helps clarify these features:
432776.1 How to Determine the Actual Size of LOB Segments and How
to Free the Deleted/Unused Space Above/Below the HWM
»
See All Articles by Columnist Jim Czuprynski
» 