Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
This chapter describes issues specific to tables that contain LOB columns. This chapter includes the following sections:
When creating tables that contain LOBs, use the guidelines described in the following sections:
You can set a persistent LOB -- that is, a LOB column in a table, or a LOB attribute in an object type that you defined-- to be NULL
or empty:
NULL
has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other datatypes.SELECT
from an empty LOB column or attribute, then you get back a locator which you can use to populate the LOB with data using supported programmatic environments, such as OCI or PL/SQL(DBMS_LOB
). See Chapter 6, "Overview of Supplied LOB APIs" for more information on supported environments.Details for these options are given in the following discussions.
You may want to set a persistent LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT
or if you want to use a SELECT
statement, such as the following, to determine whether the LOB holds a NULL value:
SELECT COUNT (*) FROM print_media WHERE ad_graphic IS NOT NULL; SELECT COUNT (*) FROM print_media WHERE ad_graphic IS NULL;
Note that you cannot call OCI or DBMS_LOB functions on a NULL LOB, so you must then use an SQL UPDATE
statement to reset the LOB column to a non-NULL (or empty) value.
The point is that you cannot make a function call from the supported programmatic environments on a LOB that is NULL.
These functions only work with a locator, and if the LOB column is NULL
, then there is no locator in the row.
You can initialize a persistent LOB to EMPTY rather that NULL. Doing so, enables you to obtain a locator for the LOB instance without populating the LOB with data. To set a persistent LOB to EMPTY, use the SQL function EMPTY_BLOB()
or EMPTY_CLOB()
in the INSERT
statement:
INSERT INTO a_table VALUES (EMPTY_BLOB());
As an alternative, you can use the RETURNING clause to obtain the LOB locator in one operation rather than calling a subsequent SELECT statement:
DECLARE Lob_loc BLOB; BEGIN INSERT INTO a_table VALUES (EMPTY_BLOB()) RETURNING blob_col INTO Lob_loc; /* Now use the locator Lob_loc to populate the BLOB with data */ END;
You can initialize the LOBs in print_media
by using the following INSERT
statement:
INSERT INTO print_media VALUES (1001, EMPTY_CLOB(), EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, NULL, NULL);
This sets the value of ad_sourcetext, ad_fltextn, ad_composite, and ad_photo to an empty value, and sets ad_graphic to NULL
.
Alternatively, LOB columns, but not LOB attributes, can be initialized to a value. That is -- persistent LOB attributes differ from persistent LOB columns in that LOB attributes cannot be initialized to a value other than NULL
or empty when the row is inserted into a table.
Note that you can initialize the LOB column to a value that contains more than 4K bytes of data.
A BFILE can be initialized to NULL or to a filename. To do so, you can use the BFILENAME() function.
The first extent of any segment requires at least 2 blocks (if FREELIST GROUPS was 0). That is, the initial extent size of the segment should be at least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent. If you try to create a LOB segment in a permanent dictionary managed tablespace with initial = 2 blocks, then it will still work because it is possible for segments in permanent dictionary managed tablespaces to override the default storage setting of the tablespaces.
But if uniform locally managed tablespaces or dictionary managed tablespaces of the temporary type, or locally managed temporary tablespaces have an extent size of 2 blocks, then LOB segments cannot be created in these tablespaces. This is because in these tablespace types, extent sizes are fixed and the default storage setting of the tablespaces is not ignored.
When selecting a datatype, consider the following topics:
Table 4-1 lists the similarities and differences between LOBs, LONGs, and LONG RAW types.
LOB Data Type | LONG and LONG RAW Data Type |
---|---|
You can store multiple LOBs in a single row |
You can store only one |
|
This is not possible with either a |
Only the LOB locator is stored in the table column; For in-line LOBs, the database will store LOBs that are less than approximately 4,000 bytes of data in the table column. |
In the case of a |
When you access a LOB column, you can choose to fetch the locator or the data. |
When you access a |
A LOB can be up to 8 terabytes or more in size depending on your block size. |
A |
There is greater flexibility in manipulating data in a random, piece-wise manner with LOBs. LOBs can be accessed at random offsets. |
Less flexibility in manipulating data in a random, piece-wise manner with |
You can replicate LOBs in both local and distributed environments. |
Replication in both local and distributed environments is not possible with a |
Varying-width character data in CLOB and NCLOB datatypes is stored in an internal format that is compatible with UCS2 Unicode character set format. This ensures that there is no storage loss of character data in a varying-width format. Also note the following if you are using LOBs to store varying-width character data:
For CLOB
and NCLOB
instances used in OCI (Oracle Call Interface), or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.
The DBMS_LOB.LOADCLOBFROMFILE
API, performs an implicit conversion from binary data to character data when loading to a CLOB or NCLOB. With the exception of DBMS_LOB.LOADCLOBFROMFILE
, LOB APIs do not perform implicit conversions from binary data to character data.
For example, when you use the DBMS_LOB.LOADFROMFILE
API to populate a CLOB
or NCLOB
, you are populating the LOB with binary data from a BFILE
. In this case, you must perform character set conversions on the BFILE
data before calling DBMS_LOB.LOADFROMFILE
.
See Also:
Oracle Database Globalization Support Guide for more detail on character set conversions. |
When designing your table, consider the following design criteria:
This section summarizes LOB storage characteristics to consider when designing tables with LOB column types.
LOB columns store locators that reference the location of the actual LOB value. Depending on the column properties you specify when you create the table, and depending the size of the LOB, actual LOB values are stored either in the table row (in-line) or outside of the table row (out-of-line).
LOB values are stored out-of-line when any of the following situations apply:
LOB values are stored in-line when any of the following conditions apply:
Using the default LOB storage properties (in-line storage) can allow for better database performance; it avoids the overhead of creating and managing out-of-line storage for smaller LOB values. If LOB values stored in your database are frequently small in size, then using in-line storage is recommended.
When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each persistent LOB column as shown in the following example:
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB) lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096 PCTVERSION 5 NOCACHE LOGGING STORAGE (MAXEXTENTS 5) );
Note: There are no tablespace or storage characteristics that you can specify for external LOBs as they are not stored in the database. |
If you need to modify the LOB storage parameters on an existing LOB column, then use the MODIFY
LOB
clause of the ALTER
TABLE
statement.
As shown in the in the previous example, specifying a name for the LOB data segment makes for a much more intuitive working environment. When querying the LOB data dictionary views USER_LOBS
, ALL_LOBS
, DBA_LOBS
(see Oracle Database Reference), you see the LOB data segment that you chose instead of system-generated names.
LOB storage characteristics that can be specified for a LOB column or a LOB attribute include the following:
TABLESPACE
PCTVERSION
or RETENTION
PCTVERSION
or RETENTION
, but not both.CACHE
/NOCACHE/CACHE READS
LOGGING/NOLOGGING
CHUNK
ENABLE
/DISABLE
STORAGE
IN
ROW
STORAGE
See the "STORAGE clause" in Oracle Database SQL Reference for more information.For most users, defaults for these storage characteristics will be sufficient. If you want to fine-tune LOB storage, then you should consider the following guidelines.
Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, then it may also be useful to specify a separate tablespace for each LOB column or attribute in order to reduce device contention.
The LOB index is an internal structure that is strongly associated with LOB storage. This implies that a user may not drop the LOB index and rebuild it.
The system determines which tablespace to use for LOB data and LOB index depending on your specification in the LOB storage clause:
When creating a table, if you specify a tablespace for the LOB index for a non-partitioned table, then your specification of the tablespace will be ignored and the LOB index will be co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.
Specifying a separate tablespace for the LOB storage segments will allow for a decrease in contention on the tablespace of the table.
When a LOB is modified, a new version of the LOB page is produced in order to support consistent read of prior versions of the LOB value.
PCTVERSION
is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION
amount of used LOB space, Oracle tries to reclaim the old versions and reuse them. In other words, PCTVERSION
is the percent of used LOB data blocks that is available for versioning old LOB data.
Default: 10 (%) Minimum: 0 (%) Maximum: 100 (%)
To decide what value PCTVERSION
should be set to, consider the following:
Table 4-2, "Recommended PCTVERSION Settings" provides some guidelines for determining a suitable PCTVERSION
value.
If your application requires several LOB updates concurrent with heavy reads of LOB columns, then consider using a higher value for PCTVERSION
, such as 20%.
Setting PCTVERSION
to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of LOB columns, it may be useful to retain old versions of LOB pages. In this case, LOB storage may grow because the database will not reuse free pages aggressively.
If persistent LOB instances in your application are created and written just once and are primarily read-only afterward, then updates are infrequent. In this case, consider using a lower value for PCTVERSION
, such as 5% or lower.
The more infrequent and smaller the LOB updates are, the less space must be reserved for old copies of LOB data. If existing LOBs are known to be read-only, then you could safely set PCTVERSION
to 0% because there would never be any pages needed for old versions of data.
As an alternative to the PCTVERSION
parameter, you can specify the RETENTION
parameter in the LOB storage clause of the CREATE TABLE
or ALTER TABLE
statement. Doing so, configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the table space. For example:
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB) lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096 RETENTION NOCACHE LOGGING STORAGE (MAXEXTENTS 5) );
The RETENTION
parameter is designed for use with Undo features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION
property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION
parameter.
Note the following with respect to the RETENTION
parameter:
RETENTION
property on a LOB column to use Undo SQL on LOB data.RETENTION
parameter explicitly. The amount of time for retention of LOB versions in determined by the UNDO_RETENTION
parameter.RETENTION
parameter is only supported in Automatic Undo Management mode. You must configure your table for use with Automatic Undo Management before you can set RETENTION
on a LOB column.RETENTION
or PCTVERSION
, but not both.
See Also:
|
When creating tables that contain LOBs, use the cache options according to the guidelines in Table 4-3, "When to Use CACHE, NOCACHE, and CACHE READS":
Cache Mode | Read ... | Written To ... |
---|---|---|
|
Frequently |
Once or occasionally |
|
Frequently |
Frequently |
|
Once or occasionally |
Never |
[NO
] LOGGING
has a similar application with regard to using LOBs as it does for other table operations. In the usual case, if the [NO
]LOGGING
clause is omitted, then this means that neither NO
LOGGING
nor LOGGING
is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
For LOBs, there is a further alternative depending on how CACHE
is stipulated.
NO
]LOGGING
clause is omitted, LOGGING
is automatically implemented (because you cannot have CACHE
NOLOGGING
).NO
]LOGGING
clause is omitted, the process defaults in the same way as it does for tables and partitioned tables. That is, the [NO
]LOGGING
value is obtained from the tablespace in which the LOB value resides.The following issues should also be kept in mind.
Regardless of whether LOGGING
or NOLOGGING
is set LOBs will never generate rollback information (undo) for LOB data pages because old LOB data is stored in versions. Rollback information that is created for LOBs tends to be small because it is only for the LOB index page changes.
NOLOGGING
is intended to be used when a customer does not care about media recovery. Thus, if the disk/tape/storage media fails, then you will not be able to recover your changes from the log because the changes were never logged.
For instance, when loading data into the LOB, if you do not care about redo and can just start the load over if it fails, set the LOB data segment storage characteristics to NOCACHE
NOLOGGING
. This provides good performance for the initial load of data.
Once you have completed loading data, if necessary, use ALTER
TABLE
to modify the LOB storage characteristics for the LOB data segment for normal LOB operations, for example, to CACHE
or NOCACHE
LOGGING
.
Set CHUNK
to the total bytes of LOB data in multiples of database block size, that is, the number of blocks that will be read or written using OCILobRead2()
, OCILobWrite2()
, DBMS_LOB
.READ()
, or DBMS_LOB
.WRITE()
during one access of the LOB value.
If only one block of LOB data is accessed at a time, then set CHUNK
to the size of one block. For example, if the database block size is 2K, then set CHUNK
to 2K.
If you explicitly specify storage characteristics for the LOB, then make sure that INITIAL
and NEXT
for the LOB data segment storage are set to a size that is larger than the CHUNK
size. For example, if the database block size is 2K and you specify a CHUNK
of 8K, then make sure that INITIAL
and NEXT
are bigger than 8K and preferably considerably bigger (for example, at least 16K).
Put another way: If you specify a value for INITIAL, NEXT
or the LOB CHUNK
size, then make sure they are set in the following manner:
You use the ENABLE
| DISABLE
STORAGE
IN
ROW
clause to indicate whether the LOB should be stored inline (in the row) or out of line.
Note: You may not alter this specification once you have made it: if you |
The default is ENABLE
STORAGE
IN
ROW
.
The maximum amount of LOB data stored in the row is the maximum VARCHAR2
size (4000). This includes the control information as well as the LOB value. If you indicate that the LOB should be stored in the row, once the LOB value and control information is larger than 4000, then the LOB value is automatically moved out of the row.
This suggests the following guidelines:
The default, ENABLE STORAGE IN ROW, is usually the best choice for the following reasons:
However, in some cases DISABLE STORAGE IN ROW is a better choice. This is because storing the LOB in the row increases the size of the row. This impacts performance if you are doing a lot of base table processing, such as full table scans, multi-row accesses (range scans), or many UPDATE/SELECT to columns other than the LOB columns.
This section discusses different techniques you can use to index LOB columns.
You might be able to improve the performance of queries by building indexes specifically attuned to your domain. Extensibility interfaces provided with the database allow for domain indexing, a framework for implementing such domain specific indexes.
See Also:
Oracle Data Cartridge Developer's Guide for information on building domain specific indexes. |
Depending on the nature of the contents of the LOB column, one of the Oracle Text options could also be used for building indexes. For example, if a text document is stored in a CLOB column, then you can build a text index to speed up the performance of text-based queries over the CLOB column.
See Also:
Oracle interMedia Reference and Oracle Text Reference, for more information regarding Oracle interMedia options. |
A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.
Function-based indexes cannot be built on nested tables. However, you can build function-based indexes on LOB columns and varrays.
Like extensible indexes and domain indexes on LOB columns, function-based indexes are also automatically updated when a DML operation is performed on the LOB column. Function-based indexes are also updated when any extensible index is updated.
See Also:
Oracle Database Application Developer's Guide - Fundamentals for more information on using function-based indexes. |
The database provides extensible indexing, a feature which enables you to define new index types as required. This is based on the concept of cooperative indexing where a data cartridge and the database build and maintain indexes for data types such as text and spatial for example, for On-line-Analytical Processing (OLAP).
The cartridge is responsible for defining the index structure, maintaining the index content during load and update operations, and searching the index during query processing. The index structure can be stored in Oracle as heap-organized, or an index-organized table, or externally as an operating system file.
To support this structure, the database provides an indextype. The purpose of an indextype is to enable efficient search and retrieval functions for complex domains such as text, spatial, image, and OLAP by means of a data cartridge. An indextype is analogous to the sorted or bit-mapped index types that are built-in within the Oracle Server. The difference is that an indextype is implemented by the data cartridge developer, whereas the Oracle kernel implements built-in indexes. Once a new indextype has been implemented by a data cartridge developer, end users of the data cartridge can use it just as they would built-in indextypes.
When the database system handles the physical storage of domain indexes, data cartridges
By supporting extensible indexes, the database significantly reduces the effort needed to develop high-performance solutions that access complex datatypes such as LOBs.
The extensible optimizer functionality allows authors of user-defined functions and indexes to create statistics collections, selectivity, and cost functions. This information is used by the optimizer in choosing a query plan. The cost-based optimizer is thus extended to use the user-supplied information.
Extensible indexing functionality enables you to define new operators, index types, and domain indexes. For such user-defined operators and domain indexes, the extensible optimizer functionality will allow users to control the three main components used by the optimizer to select an execution plan: statistics, selectivity, and cost.
You can create Oracle Text indexes on CLOB columns and perform queries on XML data.
You can partition tables that contain LOB columns. As a result, LOBs can take advantage of all of the benefits of partitioning including the following:
This section describes some of the ways you can manipulate LOBs in partitioned tables.
LOBs are supported in RANGE partitioned, LIST partitioned, and HASH partitioned tables. Composite heap-organized tables can also have LOBs.
You can partition a table containing LOB columns using the following techniques:
PARTITION BY ...
clause of the CREATE TABLE
statement.ALTER TABLE ... ADD PARTITION
clause.ALTER TABLE ... EXCHANGE PARTITION
clause. Note that EXCHANGE PARTITION
can only be used when both tables have the same storage attributes, for example, both tables store LOBs out-of-line.Creating LOB partitions at the same time you create the table (in the CREATE TABLE
statement) is recommended. If you create partitions on a LOB column when the table is created, then the column can hold LOBs stored either in-line or out-of-line LOBs.
After a table is created, new LOB partitions can only be created on LOB columns that are stored out-of-line. Also, partition maintenance operations, SPLIT PARTITION
and MERGE PARTITIONS
, will only work on LOB columns that store LOBs out-of-line. See "Restrictions for LOBs in Partitioned Index-Organized Tables" for additional information on LOB restrictions.
Note that once a table is created, storage attributes cannot be changed. See "LOB Storage" for more information about LOB storage attributes.
To improve the performance of queries, you can create indexes on partitioned LOB columns. For example:
CREATE INDEX index_name ON table_name (LOB_column_1, LOB_column_2, ...) LOCAL;
Note that only domain and function-based indexes are supported on LOB columns. Other types of indexes, such as unique indexes are not supported with LOBs.
You can move a LOB partition into a different tablespace. This is useful if the tablespace is no longer large enough to hold the partition. To do so, use the ALTER TABLE ... MOVE PARTITION
clause. For example:
ALTER TABLE current_table MOVE PARTITION partition_name TABLESPACE destination_table_space LOB (column_name) STORE AS (TABLESPACE current_tablespace);
You can split a partition containing LOBs into two equally sized partitions using the ALTER TABLE ... SPLIT PARTITION
clause. Doing so permits you to place one or both new partitions in a new tablespace. For example:
ALTER TABLE table_name SPLIT PARTITION partition_name AT (partition_range_upper_bound) INTO (PARTITION partition_name, PARTITION new_partition_name TABLESPACE new_tablespace_name LOB (column_name) STORE AS (TABLESPACE tablespace_name) ... ;
You can merge partitions that contain LOB columns using the ALTER TABLE ... MERGE PARTITIONS
clause. This technique is useful for reclaiming unused partition space. For example:
ALTER TABLE table_name MERGE PARTITIONS partition_1, partition_2 INTO PARTITION new_partition TABLESPACE new_tablespace_name LOB (column_name) store as (TABLESPACE tablespace_name) ... ;
Index Organized Tables (IOTs) support internal and external LOB columns. For the most part, SQL DDL, DML, and piece wise operations on LOBs in IOTs produce the same results as those for normal tables. The only exception is the default semantics of LOBs during creation. The main differences are:
DISABLE
STORAGE
IN
ROW
. If you forcibly try to specify an ENABLE
STORAGE
IN
ROW
clause for such LOBs, then SQL will raise an error.
On the other hand, if an overflow segment has been specified, then LOBs in index organized tables will exactly mimic their semantics in conventional tables (see "Defining Tablespace and Storage Characteristics for Persistent LOBs").
Consider the following example:
CREATE TABLE iotlob_tab (c1 INTEGER primary key, c2 BLOB, c3 CLOB, c4 VARCHAR2(20)) ORGANIZATION INDEX TABLESPACE iot_ts PCTFREE 10 PCTUSED 10 INITRANS 1 MAXTRANS 1 STORAGE (INITIAL 4K) PCTTHRESHOLD 50 INCLUDING c2 OVERFLOW TABLESPACE ioto_ts PCTFREE 10 PCTUSED 10 INITRANS 1 MAXTRANS 1 STORAGE (INITIAL 8K) LOB (c2) STORE AS lobseg (TABLESPACE lob_ts DISABLE STORAGE IN ROW CHUNK 1 PCTVERSION 1 CACHE STORAGE (INITIAL 2m) INDEX LOBIDX_C1 (TABLESPACE lobidx_ts STORAGE (INITIAL 4K)));
Executing these statements will result in the creation of an index organized table iotlob_tab
with the following elements:
iot_ts
,ioto_ts
C3
being explicitly stored in the overflow data segmentlob_ts
C2
) index segments in the tablespace lobidx_ts
C3
) data segments in the tablespace iot_ts
C3
) index segments in the tablespace iot_ts
C3
) stored in line by virtue of the IOT having an overflow segmentC2
) explicitly forced to be stored out of line
Other LOB features, such as BFILE
s and varying character width LOBs, are also supported in index organized tables, and their usage is the same as for conventional tables.
LOB columns are supported in range-, list-, and hash-partitioned index-organized tables with the following restrictions:
MOVE
, SPLIT
, and MERGE
are not supported with:
See Also:
Additional restrictions for LOB columns in general are given in "LOB Restrictions". |
To update LOBs in a nested table, you must lock the row containing the LOB explicitly. To do so, you must specify the FOR UPDATE clause in the subquery prior to updating the LOB value.
Note that locking the row of a parent table does not lock the row of a nested table containing LOB columns.
Note: Nested tables containing LOB columns are the only data structures supported for creating collections of LOBs. You cannot create a VARRAY of any LOB datatype. |