LOB (CLOB) handling within PL/SQL

Oracle 9i 9.2.0.1.0

Linux 2.4.9-e.3 Enterprise

Dear Oracles,

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 handled internally.

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.

FYI: .I think that I need CLOB fields as many of my string values are well over 4K.

.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

Tim Cornwell Received on Mon Nov 11 2002 - 13:59:06 CST