Release 2 (9.2)
Part Number A96591-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 2 (9.2) Part Number A96591-01 |
|
This chapter discusses the following topics:
When selecting a datatype, consider the following topics:
Table 7-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 |
You can store only one |
|
|
This is not possible with either a |
|
Only the LOB locator is stored in the table column; For inline LOBs, Oracle 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 |
When you access a |
|
A LOB can be up to 4 gigabytes in size. The |
|
|
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 |
Replication in both local and distributed environments is not possible with a |
Oracle does not support the replication of columns that use the LONG and LONG RAW data types. Oracle simply omits columns containing these data types from replicated tables. In Oracle9i, you must convert LONG data types to LOBs and then replicate.
Existing LONG columns can be converted to LOBs using either of the following methods:
TO_LOB() function (see "LONG to LOB Migration Using the LONG-to-LOB API" in Chapter 10, "Internal Persistent LOBs").
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.
However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.
| See: :
Oracle9i Database Globalization Support Guide for more detail on character set conversions. |
When designing your table, consider the following design criteria:
If a LOB column is NULL, no data blocks are used to store the information. The NULL value is stored in the row just like any other NULL value. This is true even when you specify DISABLE STORAGE IN ROW for the LOB.
If a LOB column is initialized with EMPTY_CLOB() or EMPTY_BLOB(), instead of NULL, a LOB locator is stored in the row. No additional storage is used.
ENABLE or DISABLE STORAGE IN ROW. In addition, an entire chunksize of data blocks is used to store the one byte of data if the LOB column was created as DISABLE STORAGE IN ROW.ENABLE STORAGE IN ROW, Oracle8i and higher only consumes one extra byte of storage in the row to store the one byte of data. If you have a LOB column created with ENABLE STORAGE IN ROW and the amount of data to store is larger than will fit in the row (approximately 4,000 bytes) Oracle uses a multiple of chunksizes to store it.When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each internal LOB.
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB) lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096 PCTVERSION 5 NOCACHE LOGGING STORAGE (MAXEXTENTS 5) );
There are no extra tablespace or storage characteristics for external LOBs since they are not stored in the database.
If you later wish to modify the LOB storage parameters, use the MODIFY LOB clause of the ALTER TABLE statement.
As shown in the "Defining Tablespace and Storage Example1" , 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 Oracle9i 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:
TABLESPACEPCTVERSIONCACHE/NOCACHE/CACHE READSLOGGING/NOLOGGINGCHUNKENABLE/DISABLE STORAGE IN ROWSTORAGE. See the "STORAGE clause" in Oracle9i SQL Reference for more information.For most users, defaults for these storage characteristics will be sufficient. If you want to fine-tune LOB storage, 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, 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 you specification in the LOB storage clause:
LOB data, the table's tablespace is used for the LOB data and index.LOB data, both the LOB data and index use the tablespace that was specified.If in creating tables in Oracle8i Release 8.1 you specify a tablespace for the LOB index for a non-partitioned table, 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 table's tablespace.
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 7-2, "Recommended PCTVERSION Settings" provides some guidelines for determining a suitable PCTVERSION value.
Several LOB updates concurrent with heavy reads of LOBs.
SET PCTVERSION = 20%
Setting PCTVERSION to twice the default allows more free pages to be used for old versions of data pages. Since large queries may require consistent reads of LOBs, it may be useful to retain old versions of LOB pages. In this case LOB storage may grow because Oracle will not reuse free pages aggressively.
LOBs are created and written just once and are primarily read-only afterward. Updates are infrequent.
SET PCTVERSION = 5% or lower
The more infrequent and smaller the LOB updates are, the less space needs to be reserved for old copies of LOB data. If existing LOBs are known to be read-only, you could safely set PCTVERSION to 0% since there would never be any pages needed for old versions of data.
When creating tables that contain LOBs, use the cache options according to the guidelines in Table 7-3, "When to Use CACHE, NOCACHE, and CACHE READS":
| Cache Mode | Read ... | Written To ... |
|---|---|---|
|
|
Frequently |
Frequently |
|
|
Once or occasionally |
Never |
|
|
Frequently |
Once or occasionally |
If you have CACHE READS set for LOBs in 8.1.6 and you downgrade to 8.1.5 or 8.0.x, your CACHE READS LOBs generate a warning and become CACHE LOGGING LOBs.
You can explicitly alter the LOBs' storage characteristics later if you do not want your LOBs to be CACHE LOGGING. For example, if you want the LOBs to be NOCACHE, use ALTER TABLE to clearly modify them to NOCACHE.
[NO] LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the normal case, if the [NO]LOGGING clause is omitted, 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, you will not be able to recover your changes from the log since 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's 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 OCILobRead(), OCILobWrite(), 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, 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, 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, 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, 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, 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.
LOBs in Oracle8i and higher can be up to 4 gigabytes. To create gigabyte LOBs, use the following guidelines to make use of all available space in the tablespace for LOB storage:
A working example of creating a tablespace and a table that can store gigabyte LOBs follows. The case applies to the multimedia application example in Chapter 10, "Internal Persistent LOBs", if the video Frame in the multimedia table is expected to be huge in size, for example, several gigabytes.
CREATE TABLESPACE lobtbs1 datafile '/your/own/data/directory/lobtbs_1.dat' size 2000M reuse online nologging default storage (maxextents unlimited); ALTER TABLESPACE lobtbs1 add datafile '/your/own/data/directory/lobtbs_2.dat' size 2000M reuse; CREATE TABLE Multimedia_tab ( Clip_ID NUMBER NOT NULL, Story CLOB default EMPTY_CLOB(), FLSub NCLOB default EMPTY_CLOB(), Photo BFILE default NULL, Frame BLOB default EMPTY_BLOB(), Sound BLOB default EMPTY_BLOB(), Voiced_ref REF Voiced_typ, InSeg_ntab InSeg_tab, Music BFILE default NULL, Map_obj Map_typ, Comments LONG ) NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab LOB(Frame) store as (tablespace lobtbs1 chunk 32768 pctversion 0 NOCACHE NOLOGGING storage(initial 100M next 100M maxextents unlimited pctincrease 0));
The difference between Example 1 and this example is that one specifies the storage clause during CREATE TABLE and one does it in CREATE TABLESPACE.
The critical factor is setting the PCTINCREASE parameter to 0. Otherwise, the default value is 50%. When a 4gigabyte LOB is being filled up, the extents size expands gradually until it blows up the tablespace, as follows:
1st extent: 100M, 2nd 100M, 3rd, 150M, 4th 225M...
See Chapter 2, "Basic LOB Components" for a basic description of LOB locators and their operations.
See Chapter 5, "Large Objects: Advanced Topics" for a description of LOB locator transaction boundaries and using read consistent locators.
This release supports binds of more than 4,000 bytes of data for LOB INSERTs and UPDATEs. In previous releases this feature was allowed for LONG columns only. You can now bind the following for INSERT or UPDATE into a LOB column:
Since you can have multiple LOBs in a row, you can bind up to 4GB data for each one of those LOBs in the same INSERT or UPDATE statement. In other words, multiple binds of more than 4,000 bytes in size are allowed in a single statement.
The bind of more than 4,000 bytes of data to a LOB column uses space from temporary tablespace. Hence ensure that your temporary tablespace is large enough to hold at least the sum of all the bind lengths for LOBs.
If your temporary tablespace is extendable, it will be extended automatically after the existing space is fully consumed. Use the following statement:
CREATE TABLESPACE .. AUTOEXTEND ON ... TEMPORARY ..;
to create an extendable temporary tablespace.
Table Multimedia_tab is described in Appendix B, "The Multimedia Schema". The following examples use an additional column called Comments. You will need to add the Comments column to table Multimedia_tab's CREATE TABLE syntax with the following line:
Comments LONG -- stores the comments of viewers on this clip
Oracle does not do any implicit conversion, such as HEX to RAW or RAW to HEX e.t.c., for data of more than 4000 bytes.
declare charbuf varchar2(32767); rawbuf raw(32767); begin charbuf := lpad ('a', 12000, 'a'); rawbuf := utl_raw.cast_to_raw(charbuf);
Table 7-4, "Binds of More Than 4,000 Bytes: Allowed INSERT and UPDATE Operations", outlines which INSERT operations are allowed in the preceding example and which are not. The same cases also apply to UPDATE operations.
If you bind more than 4,000 bytes of data to a BLOB or a CLOB, and the data consists of an SQL operator, then Oracle limits the size of the result to at most 4,000 bytes.
The following statement inserts only 4,000 bytes because the result of LPAD is limited to 4,000 bytes:
INSERT INTO Multimedia_tab (story) VALUES (lpad('a', 5000, 'a'));
The following statement inserts only 2,000 bytes because the result of LPAD is limited to 4,000 bytes, and the implicit hex to raw conversion converts it to 2,000 bytes of RAW data:
INSERT INTO Multimedia_tab (sound) VALUES (lpad('a', 5000, 'a'));
The following lists the restrictions for binds of more than 4,000 bytes:
CREATE TABLE foo (a INTEGER ); DECLARE bigtext VARCHAR2(32767); smalltext VARCHAR2(2000); bigraw RAW (32767); BEGIN bigtext := LPAD('a', 32767, 'a'); smalltext := LPAD('a', 2000, 'a'); bigraw := utlraw.cast_to_raw (bigtext); /* The following is allowed: */ INSERT INTO Multimedia_tab(clip_id, story, frame, comments) VALUES (1,bigtext, bigraw,smalltext); /* The following is allowed: */ INSERT INTO Multimedia_tab (clip_id, story, comments) VALUES (2,smalltext, bigtext); bigtext := LPAD('b', 32767, 'b'); smalltext := LPAD('b', 20, 'a'); bigraw := utlraw.cast_to_raw (bigtext); /* The following is allowed: */ UPDATE Multimedia_tab SET story = bigtext, frame = bigraw, comments = smalltext; /* The following is allowed */ UPDATE Multimedia_tab set story = smalltext, comments = bigtext; /* The following is NOT allowed because we are trying to insert more than 4000 bytes of data in a LONG and a LOB column: */ INSERT INTO Multimedia_tab (clip_id, story, comments) VALUES (5, bigtext, bigtext); /* The following is NOT allowed because we are trying to insert data into LOB attribute */ INSERT into Multimedia_tab (clip_id,map_obj) VALUES (10,map_typ(NULL, NULL, NULL, NULL, NULL,bigtext, NULL)); /* The following is not allowed because we try to perform INSERT AS SELECT data INTO LOB */ INSERT INTO Multimedia_tab (story) AS SELECT bigtext FROM foo; END;
For binds of more than 4,000 bytes, inserts are not supported because hex-to-raw and raw-to-hex conversions are not supported.
/* Oracle does not do any implicit conversion (e.g., HEX to RAW or RAW to HEX etc.) for data of more than 4000 bytes. Hence, the following cases will not work : */ declare charbuf varchar2(32767); rawbuf raw(32767); begin charbuf := lpad ('a', 12000, 'a'); rawbuf := utl_raw.cast_to_raw(charbuf); /* The following is allowed ... */ INSERT INTO Multimedia_tab (story, sound) VALUES (charbuf, rawbuf); /* The following is not allowed because Oracle won't do implicit hex to raw conversion. */ INSERT INTO Multimedia_tab (sound) VALUES (charbuf); /* The following is not allowed because Oracle won't do implicit raw to hex conversion. */ INSERT INTO Multimedia_tab (story) VALUES (rawbuf); /* The following is not allowed because we can't combine the utl_raw.cast_to_raw() operator with the bind of more than 4,000 bytes. */ INSERT INTO Multimedia_tab (sound) VALUES (utl_raw.cast_to_raw(charbuf)); end; /
If you bind more than 4,000 bytes of data to a BLOB or a CLOB, and the data actually consists of an SQL operator, then Oracle limits the size of the result to 4,000 bytes.
For example, /* The following command inserts only 4,000 bytes because the result of LPAD is limited to 4,000 bytes */ INSERT INTO Multimedia_tab (story) VALUES (lpad('a', 5000, 'a')); /* The following command inserts only 2,000 bytes because the result of LPAD is limited to 4,000 bytes, and the implicit hex to raw conversion converts it to 2,000 bytes of RAW data. */ INSERT INTO Multimedia_tab (sound) VALUES (lpad('a', 5000, 'a'));
CREATE TABLE foo( a INTEGER ); void insert() /* A function in an OCI program */ { /* The following is allowed */ ub1 buffer[8000]; text *insert_sql = "INSERT INTO Print_media(ad_sourcetext, ad_composite, comments) VALUES (:1, :2, :3)"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, SQLT_LBI, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* The following is allowed */ ub1 buffer[8000]; text *insert_sql = "INSERT INTO Print_media (ad_sourcetext,comments) VALUES (:1, :2)"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* The following is allowed, no matter how many rows it updates */ ub1 buffer[8000]; text *insert_sql = (text *)"UPDATE Print_media SET ad_sourcetext = :1, ad_photo=:2, comments=:3"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, SQLT_LBI, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* The following is allowed, no matter how many rows it updates */ ub1 buffer[8000]; text *insert_sql = (text *)"UPDATE Print_media SET ad_sourcetext = :1, ad_photo=:2, comments=:3"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* Piecewise, callback and array insert/update operations similar to the allowed regular insert/update operations are also allowed */ } void insert() { /* The following is NOT allowed because we try to insert >4000 bytes to both LOB and LONG columns */ ub1 buffer[8000]; text *insert_sql = (text *)"INSERT INTO Print_media (ad_composite, comments) VALUES (:1, :2)"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* The following is NOT allowed because we try to insert data into LOB attributes */ ub1 buffer[8000]; text *insert_sql = (text *)"INSERT INTO Print_media (adheader_typ) VALUES (adheader_typ(NULL, NULL, NULL, NULL, NULL,:1, NULL))"; OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 2000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* The following is NOT allowed because we try to do insert as select character data into LOB column */ ub1 buffer[8000]; text *insert_sql = (text *)"INSERT INTO Print_media (ad_sourcetext) SELECT :1 from FOO"; OCIStmtPrepare(stmthp, errhp, insert_sql,strlen((char*)insert_sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer, 8000, SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT); } void insert() { /* Other update operations similar to the disallowed insert operations are also not allowed. Piecewise and callback insert/update operations similar to the disallowed regular insert/update operations are also not allowed */ }
The OPEN, CLOSE, and ISOPEN interfaces let you open and close an internal LOB and test whether an internal LOB is already open.
It is not mandatory that you wrap all LOB operations inside the OPEN/CLOSE APIs. The addition of this feature does not impact already-existing applications that write to LOBs without first opening them, since these calls did not exist in 8.0.
|
Note: Openness is associated with the |
LOB will implicitly open and close the LOB thereby firing any triggers on a domain index. Note that in this case, any domain indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, domain LOB indexes are always valid and may be used at any time.LOB modification. Instead, the trigger on domain indexes will be fired at the CLOSE call. For example, you might design your application so that domain indexes are not be updated until you call CLOSE. However, this means that any domain indexes on the LOB will not be valid in-between the OPEN/CLOSE calls.It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed but but the domain and functional indexes are not updated. If this happens, please rebuild your functional and domain indexes on the LOB column.
At transaction rollback, the openness of all open LOBs still open for that transaction are discarded. Discarding the openness means the following for LOBs:
A 'transaction' where an open LOB value must be closed meets one of the following:
SELECT ... FOR UPDATE)' and COMMITA LOB opened when there is no transaction, must be closed before the end of the session. If there are still open LOBs at the end of the session, the openness will be discarded and no triggers on domain indexes will be fired.
It is also an error to open/close the same LOB twice either with different locators or with the same locator.
This example shows the correct use of open and close calls to LOBs inside and outside a transaction.
DECLARE Lob_loc1 CLOB; Lob_loc2 CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 1; BEGIN /* Select a LOB: */ SELECT Story INTO Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; /* The following statement opens the LOB outside of a transaction so it must be closed before the session ends: */ DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY); /* The following statement begins a transaction. Note that Lob_loc1 and Lob_loc2 point to the same LOB: */ SELECT Story INTO Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 1 for update; /* The following LOB open operation is allowed since this lob has not been opened in this transaction: */ DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READWRITE); /* Fill the buffer with data to write to the LOB */ buffer := 'A good story'; Amount := 12; /* Write the buffer to the LOB: */ DBMS_LOB.WRITE(Lob_loc2, Amount, Position, Buffer); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE(Lob_loc2); /* The COMMIT ends the transaction. It is allowed because all LOBs opened in the transaction were closed. */ COMMIT; /* The the following statement closes the LOB that was opened before the transaction started: */ DBMS_LOB.CLOSE(Lob_loc1); END;
This example the incorrect use of OPEN and CLOSE calls to a LOB and illustrates how committing a transaction which has open LOBs returns an error.
DECLARE Lob_loc CLOB; BEGIN /* Note that the FOR UPDATE clause starts a transaction: */ SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 for update; DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); /* COMMIT returns an error because there is still an open LOB associated with this transaction: */ COMMIT; END;
Index Organized Tables (IOT) now support internal and external LOB columns. The SQL DDL, DML and piece wise operations on LOBs in IOT exhibit the same behavior as for conventional tables. The only exception is the default behavior of LOBs during creation. The main differences are:
LOB's data and index segments will be created in the tablespace in which the primary key index segments of the index organized table are created.LOBs in an index organized table created without an overflow segment will be stored out of line. In other words, if an index organized table is created without an overflow segment, the LOBs in this table have their default storage attributes as DISABLE STORAGE IN ROW. If you forcibly try to specify an ENABLE STORAGE IN ROW clause for such LOBs, SQL will raise an error.
On the other hand, if an overflow segment has been specified, LOBs in index organized tables will exactly mimic their behavior in conventional tables (see "Defining Tablespace and Storage Characteristics for Internal 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_tsC3 being explicitly stored in the overflow data segmentlob_tsC2) index segments in the tablespace lobidx_tsC3) data segments in the tablespace iot_tsC3) index segments in the tablespace iot_tsC3) 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 BFILEs and varying character width LOBs, are also supported in index organized tables, and their usage is the same as for conventional tables.
You can partition tables with LOBs. As a result, LOBs can take advantage of all of the benefits of partitioning. For example, LOB segments can be spread between several tablespaces to balance I/O load and to make backup and recovery more manageable. LOBs in a partitioned table also become easier to maintain.
This section describes some of the ways you can manipulate LOBs in partitioned tables.
As an extension to the example multimedia application described in Appendix B, "The Multimedia Schema", let us suppose that makers of a documentary are producing multiple clips relating to different Presidents of the United States. The clips consist of photographs of the presidents accompanied by spoken text and background music. The photographs come from the PhotoLib_Tab archive. To make the most efficient use of the presidents' photographs, they are loaded into a database according to the structure illustrated in Figure 7-1.
The columns in Multimedia_tab are described in Table 7-5, "Multimedia_tab Columns".
To isolate the photographs associated with a given president, a partition is created for each president by the ending dates of their terms of office. For example, a president who served two terms would have two partitions: the first partition bounded by the end date of the first term and a second partition bounded by the end date of the second term.
CREATE TABLE Presidentphoto_tab(PresName VARCHAR2(30), PhotoDate DATE, PhotoName VARCHAR2(30), PresPhoto BLOB, Script CLOB, Actor VARCHAR2(30), Music BFILE) STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) LOB (PresPhoto) STORE AS (CHUNK 4096) LOB (Script) STORE AS (CHUNK 2048) PARTITION BY RANGE(PhotoDate) (PARTITION GeorgeWashington1_part /* Use photos to the end of Washington's first term */ VALUES LESS THAN (TO_DATE('19-mar-1792', 'DD-MON-YYYY')) TABLESPACE EarlyPresidents_tbs LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs) LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs), PARTITION GeorgeWashington2_part /* Use photos to the end of Washington's second term */ VALUES LESS THAN (TO_DATE('19-mar-1796', 'DD-MON-YYYY')) TABLESPACE EarlyPresidents_tbs LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs) LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs), PARTITION JohnAdams1_part /* Use photos to the end of Adams' only term */ VALUES LESS THAN (TO_DATE('19-mar-1800', 'DD-MON-YYYY')) TABLESPACE EarlyPresidents_tbs LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs) LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs), /* ...intervening presidents... */ PARTITION RichardNixon1_part /* Use photos to the end of Nixon's first term */ VALUES LESS THAN (TO_DATE('20-jan-1972', 'DD-MON-YYYY')) TABLESPACE RichardNixon1_tbs LOB (PresPhoto) store as (TABLESPACE Post1960PresidentsPhotos_tbs) LOB (Script) store as (TABLESPACE Post1960PresidentsScripts_tbs) );
To improve the performance of queries which access records by a President's name and possibly the names of photographs, a UNIQUE local index is created:
CREATE UNIQUE INDEX PresPhoto_idx ON PresidentPhoto_tab (PresName, PhotoName, Photodate) LOCAL;
As a part of upgrading from Oracle8.0 to 8.1 or higher, data was exchanged from an existing non-partitioned table containing photos of Bill Clinton's first term into the appropriate partition:
ALTER TABLE PresidentPhoto_tab EXCHANGE PARTITION RichardNixon1_part WITH TABLE Mirrorlob_tab INCLUDING INDEXES;
To account for Richard Nixon's second term, a new partition was added to PresidentPhoto_tab:
ALTER TABLE PresidentPhoto_tab ADD PARTITION RichardNixon2_part VALUES LESS THAN (TO_DATE('20-jan-1976', 'DD-MON-YYYY')) TABLESPACE RichardNixon2_tbs LOB (PresPhoto) store as (TABLESPACE Post1960PresidentsPhotos_tbs) LOB (Script) store as (TABLESPACE Post1960PresidentsScripts_tbs);
During his second term, Richard Nixon had so many photo-opportunities, that the partition containing information on his second term is no longer adequate. It was decided to move the data partition and the corresponding LOB partition of PresidentPhoto_tab into a different tablespace, with the corresponding LOB partition of Script remaining in the original tablespace:
ALTER TABLE PresidentPhoto_tab MOVE PARTITION RichardNixon2_part TABLESPACE RichardNixonBigger2_tbs LOB (PresPhoto) STORE AS (TABLESPACE RichardNixonPhotos_tbs);
When Richard Nixon was re-elected for his second term, a partition with bounds equal to the expected end of his term (20-jan-1976) was added to the table (see the preceding example.) Since Nixon resigned from office on 9 August 1974, that partition had to be split to reflect the fact that the remainder of the term was served by Gerald Ford:
ALTER TABLE PresidentPhoto_tab SPLIT PARTITION RichardNixon2_part AT (TO_DATE('09-aug-1974', 'DD-MON-YYYY')) INTO (PARTITION RichardNixon2_part, PARTITION GeraldFord1_part TABLESPACE GeraldFord1_tbs LOB (PresPhoto) STORE AS (TABLESPACE Post1960PresidentsPhotos_tbs) LOB (Script) STORE AS (TABLESPACE Post1960PresidentsScripts_tbs));
Despite the best efforts of the documentary producers in searching for photographs of paintings or engravings of George Washington, the number of photographs that were found was inadequate to justify a separate partition for each of his two terms. Accordingly, it was decided to merge these two partition into one named GeorgeWashington8Years_part:
ALTER TABLE PresidentPhoto_tab MERGE PARTITIONS GeorgeWashington1_part, GeorgeWashington2_part INTO PARTITION GeorgeWashington8Years_part TABLESPACE EarlyPresidents_tbs LOB (PresPhoto) store as (TABLESPACE EarlyPresidentsPhotos_tbs) LOB (Script) store as (TABLESPACE EarlyPresidentsScripts_tbs);
You cannot build B-tree or bitmap indexes on a LOB column. However, depending on your application and its usage of the LOB column, you might be able to improve the performance of queries by building indexes specifically attuned to your domain. Oracle8i and higher's extensibility interfaces allow for domain indexing, a framework for implementing such domain specific indexes.
| See Also:
Oracle9i 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 interMedia options could also be used for building indexes. For example, if a text document is stored in a CLOB column, you can build a text index (provided by Oracle) to speed up the performance of text-based queries over the CLOB column.
| See Also:
Oracle interMedia User's Guide and Reference and Oracle Text Reference, for more information regarding Oracle's interMedia options. |
Oracle9i now supports functional indexing on LOB columns. Analogous to extensible/domain indexes on LOB columns, functional indexes are also automatically updated when a DML operation is performed on the LOB column.
|
Note: When extensible indexes are being updated, if any functional indexes are present on the LOB column, they are also updated. |
This section describes the following topics:
In prior releases, you could only access LOBs stored in the database using LOB locators through a set of APIs in various language interfaces (C, C++, OO4O, Java, COBOL, PL/SQL). LOBs could not be used in SQL character functions.
In Oracle9i, for the first time, you can access LOBs using SQL VARCHAR2 semantics, such as SQL string operators and functions.
By providing you with an SQL interface, which you are familiar with, accessing LOB data can be greatly facilitated. You can benefit from this added functionality in the following two cases:
Advanced LOB users who need to take advantage of features such as random access and piecewise fetch, should continue using existing LOB API interfaces.
For users of medium-to-large sized (> 1M) LOBs, this SQL interface is not advised due to possible performance issues.
This description is limited to internal persistent LOBs only. This release, does not offer SQL support on BFILEs.
|
Note: SQL Semantics Support has no impact on current usage of LOBs. Existing LOB applications, using LOB APIs, do not need to be changed. |
The following SQL VARCHAR2 functions and operators are now allowed for CLOBs, as indicated in Table 7-6:
For LONG to LOB migration, the following relational operators in PL/SQL now work on LONGs and LOBs:
These operators are also listed in Table 7-6.
The following CHAR to CLOB conversion functions are now allowed for LOBs:
The following SQL functionality is not supported for LOBs because the functions are either infrequently used or have easy workarounds.
Table 7-6, lists all SQL operators and functions that take a VARCHAR2 as operands/arguments, or return a VARCHAR2 value. With the only exception of the "IS [NOT] NULL" operator, none of the operators/functions in prior releases work on CLOBs.
In Table 7-6, the SQL operators/functions supported on CLOBs in Oracle9i, are indicated in the 4th "SQL" column.
Most functions listed in Table 7-6 also apply to PL/SQL built-in functions (supplied packages). The 5th "PL/SQL" column indicates the availability of the operator/function on CLOBs in PL/SQL.
Implicit conversions between CLOBs and CHAR types are enabled in Oracle9i. Therefore, functions not yet enabled for CLOBs can still accept CLOBs through implicit conversion. In this case, CLOBs are converted to a CHAR or a VARCHAR2 before the function is invoked. If the CLOB is greater than 4K bytes in size, only 4000 bytes will be converted into CHARs or VARCHAR2s.
In Table 7-6, the functions which take CLOB parameters through implicit conversions, are denoted as "CNV".
| Category | Operator / Function | SQL Example for CLOB Columns | SQL | PL/SQL |
|---|---|---|---|---|
|
Concat |
||, CONCAT() |
Select clobCol || clobCol2 from tab; |
Yes |
Yes |
|
Comparison |
= , !=, >, >=, <, <=, <>, ^= |
if clobCol=clobCol2 then... |
No |
Yes |
|
Comparison |
IN, NOT IN |
if clobCol NOT IN (clob1, clob2, clob3) then... |
No |
Yes |
|
Comparison |
SOME, ANY, ALL |
if clobCol < SOME (select clobCol2 from...) then... |
No |
N/A |
|
Comparison |
BETWEEN |
if clobCol BETWEEN clobCol2 and clobCol3 then... |
No |
Yes |
|
Comparison |
LIKE [ESCAPE] and its variants, see Table 7-7. |
if clobCol LIKE `%pattern%' then... |
Yes |
Yes |
|
Comparison |
IS [NOT] NULL |
where clobCol IS NOT NULL |
Yes |
Yes |