Skip Headers

Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)

Part Number A96591-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

9
LOBS: Best Practices

This chapter discusses the following topics:

Using SQL*Loader

You can use SQL*Loader to bulk load LOBs.

See:

Loading XML Documents Into LOBs With SQL*Loader

Because LOBs can be quite large, SQL*Loader can load LOB data from either the main datafile (inline with the rest of the data) or from LOBFILEs. Figure 9-1 shows the LOBFILE syntax.

Figure 9-1 The LOBFILE Syntax

Text description of apa30.gif follows.

Text description of the illustration apa30.gif

LOB data can be lengthy enough that it makes sense to load it from a LOBFILE. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs). Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64K chunks. To load physical records larger than 64K, you can use the READSIZE parameter to specify a larger size.

It is best to load XMLType columns or columns containing XML data in CLOBs, using LOBFILEs.

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files.

A direct-path load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in Chapter 9 of Oracle9i Database Utilities.

Figure 9-2 illustrates SQL*Loader's direct-path load and conventional path loads.

Tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

Figure 9-2 SQL*Loader: Direct-Path and Conventional Path Loads

Text description of sutil018.gif follows.

Text description of the illustration sutil018.gif

LOB Performance Guidelines

General Performance Guidelines

Use the following guidelines to achieve maximum performance with LOBs:

Some Performance Numbers

Table 9-1 lists the results of a performance test that enqueued 500 messages using a chunk size of 8KB for the LOB part of the payload. This performance test used Oracle8i Release 3 (8.1.7), and a DB_BLOCKSIZE = 8192 (8K), identical to the operating system block size.

Table 9-1 Response Time When Enqueueing 500 Messages With and Without CACHE and LOGGING
CHUNK SIZE CACHE (Y/N) LOGGING (Y/N) MESSAGE_SIZE RESPONSE TIME

8k

NOCACHE

NOLOGGING

3900 bytes

01:33 sec

8k

NOCACHE

NOLOGGING

4000 bytes

06:19 sec

8k

NOCACHE

NOLOGGING

4000 bytes

04:36 sec

8k

CACHE

3900 bytes

01:22 sec

8k

CACHE

4000 bytes

01:22 sec

8k

CACHE

4000 bytes

01:83 sec

8k

CACHE

20000 bytes

02:33 sec

Previous response times using a 16k chunksize, NOCACHE, and NOLOGGING for a message of 4000 bytes was 12:28 sec.

These results indicate that the CACHE parameter is the parameter giving the best performance improvement.

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:

Moving Data to LOBs in a Threaded Environment

Incorrect procedure

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

  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.

The Correct Procedure


Note:
  • There is no need to 'create' an empty LOB.
  • 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.

Hence the preferred 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.

See Also:

Chapter 14, "LOBs Case Studies"

Migrating from LONGs to LOBs

During migration from LONGs to LOBs, the redo changes for the table are logged only if the table has LOGGING on. Redo changes for the column being converted from LONG to LOB are logged only if the storage characteristics of the LOB indicate LOGGING. The default value for LOGGING|NOLOGGING for the LOB is inherited from the tablespace in which the LOB is being created.

Preventing Generation of Redo Space During Migration

To prevent generation of redo space during migration and migrate smoothly, use the following statements:

  1. ALTER TABLE Long_tab NOLOGGING;
  2. ALTER TABLE Long_tab MODIFY ( long_col CLOB [default <default_val>]) LOB (long_col) STORE AS (... NOLOGGING ...);
  3. ALTER TABLE Long_tab MODIFY LOB long_col STORE AS (...LOGGING...);
  4. ALTER TABLE Long_tab LOGGING;
  5. Take a backup of the tablespaces containing the table and the LOB.

    See Also:

    Chapter 8, "Migrating From LONGs to LOBs"


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback