LOB (CLOB) handling in PL/SQL
Oracle 9i 188.8.131.52.0
Linux 2.4.9-e.3 Enterprise
Hello and thanks for looking.
I am currently constructing a new database that will contain only a few (5-
10) tables, but some will have a fairly large volume (multiple millions)
of records. The row data for some of the large tables contain CLOB types,
and these seem to be problematic as far as the speed with which they are
I have a fair number of PL/SQL procedures that create some of the CLOB
field values from data held within the other tables, and my experience is
that dealing with CLOBS is something that may require considerable tuning
to reduce the extent to which internal temporary CLOB fields are created
and when or how they are copied.
I have searched through the Oracle documentation, and found some
information (LOBS: Best Practices) on handling these types, but there are
only a few vague warnings about what will happen internally when
operations on LOBs are performed. The general message is that CLOB field
operations will produce temporary internal values (of Session duration)
unless they are explicitly created and destroyed.
This is fine, and I understand how this is important, but I haven't seen a
very good description of how, say, to construct a long string and insert
it into a CLOB column without incurring some penalty by the creation of
multiple internal CLOB fields to support the operation.
In my particular case, I am creating CLOB field values from other tables
with CLOB values by concatenating them within PL/SQL scripts. I also
concatenate fixed text strings along in the concatenated LOBs and produce
a result that is inserted into a target record.
What I lack is the expertise on how best to construct these strings while
incurring the least amount of internal overhead.
I think that I need CLOB fields as many of my string values are well over
I have found that typical SQL inserts that build these fields on the fly
within an insert statement will consume HUGE amounts of temp tablespace
(many, many gigabytes for a small recordset), and so I have taken to
iteratively updating each record with a separately called procedure. This
is quite slow - IMHO.
I have also found that copying rows (new inserts) containing CLOB fields
is quite slow too.
Any thoughts or documents that someone can suggest will be appreciated.
Thanks In Advance
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL