Skip Headers

Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)

Part Number B10796-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Performance Guidelines

This chapter discusses the following topics:

LOB Performance Guidelines

This section describes performance guidelines for applications that use LOB datatypes.

Performance Guidelines for Small Size LOBs

If most LOBs in your database tables are small in size--8K bytes or less--and only a few rows have LOBs larger than 8K bytes, then use the following guidelines to maximize database performance:

General Performance Guidelines

Use the following guidelines to achieve maximum performance with LOBs:

Temporary LOB Performance Guidelines

In addition to the guidelines described earlier under "LOB Performance Guidelines" on LOB performance in general, here are some guidelines for using temporary LOBs:

Performance Considerations for SQL Semantics and LOBs

Be aware of the following performance issues when using SQL semantics with LOBs:

Moving Data to LOBs in a Threaded Environment

There two procedures that you can use to move data to LOBs in a threaded environment, one of which should be avoided.

Procedure to Avoid

The following sequence requires a new connection when using a threaded environment, adversely affects performance, and is not recommended:

  1. Create an empty (non-NULL) LOB
  2. INSERT using the empty LOB
  3. SELECT-FOR-UPDATE of the row just entered
  4. Move data into the LOB
  5. COMMIT. This releases the SELECT-FOR-UPDATE locks and makes the LOB data persistent.

Recommended Procedure

  • There is no need to create an empty LOB in this procedure.
  • You can use the RETURNING clause as part of the INSERT/UPDATE statement to return a locked LOB locator. This eliminates the need for doing a SELECT-FOR-UPDATE, as mentioned in step 3.

The recommended procedure is as follows:

  1. INSERT an empty LOB, RETURNING the LOB locator.
  2. Move data into the LOB using this locator.
  3. COMMIT. This releases the SELECT-FOR-UPDATE locks, and makes the LOB data persistent.

Alternatively, you can insert >4,000 byte of data directly for the LOB columns but not the LOB attributes.