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 |
|
Character Functions |
INITCAP, NLS_INITCAP |
select INITCAP(clobCol) from... |
CNV |
CNV |
|
Character Functions |
LOWER, NLS_LOWER, UPPER, NLS_UPPER |
...where LOWER(clobCol1) = LOWER(clobCol2) |
Yes |
Yes |
|
Character Functions |
LPAD, RPAD |
select RPAD(clobCol, 20, ' La') from... |
Yes |
Yes |
|
Character Functions |
TRIM, LTRIM, RTRIM |
...where RTRIM(LTRIM(clobCol,'ab'), `xy') = `cd' |
Yes |
Yes |
|
Character Functions |
REPLACE |
select REPLACE(clobCol, `orig','new') from... |
Yes |
Yes |
|
Character Functions |
SOUNDEX |
...where SOUNDEX(clobCOl) = SOUNDEX(`SMYTHE') |
CNV |
CNV |
|
Character Functions |
SUBSTR and its variants, see Table 7-7. |
...where substr(clobCol, 1,4) = `THIS' |
Yes |
Yes |
|
Character Functions |
TRANSLATE |
select TRANSLATE(clobCol, `123abc','NC') from... |
CNV |
CNV |
|
Character Functions |
ASCII |
select ASCII(clobCol) from... |
CNV |
CNV |
|
Character Functions |
INSTR and its variants, see Table 7-7. |
...where instr(clobCol, `book') = 11 |
Yes |
Yes |
|
Character Functions |
LENGTH and its variants, see Table 7-7. |
...where length(clobCol) != 7; |
Yes |
Yes |
|
Character Functions |
NLSSORT |
...where NLSSORT (clobCol,'NLS_SORT = German') > NLSSORT ('S','NLS_SORT = German') |
CNV |
CNV |
|
Conversion |
CHARTOROWID |
CHARTOROWID(clobCol) |
CNV |
CNV |
|
Conversion |
HEXTORAW |
HEXTORAW(CLOB) |
No |
CNV |
|
Conversion |
CONVERT |
select CONVERT(clobCol,'WE8DEC','WE8HP') from... |
Yes |
CNV |
|
Conversion |
TO_DATE |
TO_DATE(clobCol) |
CNV |
CNV |
|
Conversion |
TO_NUMBER |
TO_NUMBER(clobCol) |
CNV |
CNV |
|
Conversion |
TO_TIMESTAMP |
TO_TIMESTAMP(clobCol) |
No |
CNV |
|
Conversion |
TO_SINGLE_BYTE |
TO_SINGLE_BYTE(clobCol) |
CNV |
CNV |
|
Conversion |
TO_CHAR |
TO_CHAR(clobCol) |
Yes |
Yes |
|
Conversion |
TO_NCHAR |
TO_NCHAR(clobCol) |
Yes |
Yes |
|
Conversion |
TO_LOB |
INSERT INTO... SELECT TO_LOB(longCol)... Note: TO_LOB can only be used to create or insert into a table with LOB columns as SELECT FROM a table with a LONG column. |
N/A |
N/A |
|
Conversion |
TO_CLOB |
TO_CLOB(varchar2Col) |
Yes |
Yes |
|
Conversion |
TO_NCLOB |
TO_NCLOB(varchar2Clob) |
Yes |
Yes |
|
Aggregate Functions |
COUNT |
select count(clobCol) from... |
No |
N/A |
|
Aggregate Functions |
MAX, MIN |
select MAX(clobCol) from... |
No |
N/A |
|
Aggregate Functions |
GROUPING |
select grouping(clobCol) from... group by cube (clobCol); |
No |
N/A |
|
Other Functions |
GREATEST, LEAST |
select GREATEST (clobCol1, clobCol2) from... |
No |
CNV |
|
Other Functions |
DECODE |
select DECODE(clobCol, condition1, value1, defaultValue) from... |
CNV |
CNV |
|
Other Functions |
NVL |
select NVL(clobCol,'NULL') from... |
Yes |
Yes |
|
Other Functions |
DUMP |
select DUMP(clobCol) from... |
No |
N/A |
|
Other Functions |
VSIZE |
select VSIZE(clobCol) from... |
No |
N/A |
| See Also:
Oracle9i SQL Reference, Chapter 6, "Functions". |
In this release, database UNICODE support for VARCHAR2s [unicode] provides a few UNICODE variants on functions INSTR, SUBSTR, LENGTH, and LIKE. These UNICODE functions are not yet supported on CLOBs or NCLOBs as summarized in Table 7-7.
See Also:
for a detailed description on the usage of UNICODE functions. |
Table 7-8 lists other SQL features where LOBs cannot be used. Refer to the "LOB Restrictions" section in Chapter 4, "Managing LOBs", for further details.
With Oracle9i, you can retrieve data from LOBs directly using SQL without using any special LOB API.
In PL/SQL, you can define a VARCHAR2 for a CLOB and RAW for a BLOB column. You can also define CLOBs/BLOBs for VARCHAR2/RAW columns.
In PL/SQL, if a CLOB column is selected into a local VARCHAR2 variable, data stored in the CLOB column is retrieved and put into the CHAR buffer. If the buffer is not large enough to contain all the CLOB data a truncation error is raised and no data is written to the buffer. After the SELECT, the VARCHAR2 variable behaves the same as a regular character buffer.
In contrast, when a CLOB column is selected into a local CLOB variable, the CLOB locator is fetched. PL/SQL built-in functions that previously took only VARCHAR2s are now enabled to also take CLOB locators as arguments. The return type of the functions is CLOB if the primary argument is a CLOB. At the same time, the CLOB local variable can behave as a LOB locator when passed to DBMS_LOB APIs.
The preceding statement also applies to RAWs and BLOBs.
SQL operators/functions that currently take VARCHAR2 columns as operands or arguments are now enabled to accept CLOB columns. Previously, in Oracle8i, comparison of LOBs was not allowed, except for comparing LOB functions and the `IS [NOT] NULL' operator on LOBs. In this release, comparison of LOBs themselves in PL/SQL is allowed, while comparison in SQL queries is not yet available for performance concerns.
SQL operators/functions that previously returned VARCHAR2s, now either return a CLOB or a VARCHAR2, depending on the input parameter type.
Operators/functions continue to return VARCHAR2s when only VARCHAR2s are passed in as arguments. A function with only VARCHAR2 parameters never returns a CLOB.
Operators/functions return CLOBs when the primary argument, usually the first parameter is passed in as CLOBs. For example, the following SQL statements select out results as CLOB types:
SELECT SUBSTR(clobCol, 1,4) FROM .... WHERE LENGTH(clobCol)>4; SELECT clobCol1 || charCol1 FROM ...;
|
Note: For functions like CONCAT(),||, which do not have a well-defined primary argument, if any parameter is a LOB, the function returns a LOB. |
When a LOB is returned, the result from the select list is in the form of a temporary LOB locator. Your application should view the temporary LOB as local storage for the CHAR string returned from the SELECT. In PL/SQL, the temporary LOB has the same lifetime (duration) as other local PL/SQL program variables. It can be passed to subsequent SQL or PL/SQL VARCHAR2 functions or queries:
DBMS_LOB.FREETEMPORARY() call to release the resources taken by the local temporary LOBs.Alternatively, if any of the following transpire:
the returned result is a regular CHAR buffer with the declared size. If the VARCHAR2 buffer is not large enough to fit the data from the LOB, a truncation error is raised.
The following example illustrates selecting out a CLOB column into a VARCHAR2 and returning the result as a CHAR buffer of declared size:
DECLARE vc1 VARCHAR2(32000); lb1 CLOB; lb2 CLOB; BEGIN SELECT clobCol1 INTO vc1 FROM tab WHERE colID=1; -- lb1 is a temporary LOB SELECT clobCol2 || clobCol3 INTO lb1 FROM tab WHERE colID=2; lb2 := vc1|| lb1; -- lb2 is a still temporary LOB, so the persistent data in the database -- is not modified. An update is necessary to modify the table data. UPDATE tab SET clobCol1 = lb2 WHERE colID = 1; DBMS_LOB.FREETEMPORARY(lb2); -- Free up the space taken by lb2 <... some more queries ...>
END; -- at the end of the block, lb1 is automatically freed
For LOB columns, operator "IS [NOT] NULL" has been allowed since Oracle8. It checks if there is a LOB locator stored in the table row.
For VARCHAR2 columns, operator "IS NULL" indicates an empty string, or a null string.
SQL RAW types and BLOBs are handled as follows:
In Oracle9i, there has been no significant change to SQL DML with regards to LOBs. The only related change is in the WHERE clause of UPDATE and DELETE. In prior releases, Oracle did not allow LOBs in the WHERE clause of UPDATE, DELETE, and SELECT. Now, SQL functions of LOBs that do not involve comparing LOB values, are allowed in predicates of the WHERE. clause. For example, length() and insert().
As listed in Table 7-6 through Table 7-8, the SQL functions/operators for VARCHAR2s/RAWs have been extended to work on CLOB or BLOB columns.
The return type of the SQL functions depend on the input type. Refer to "Returning CLOB Values from SQL Functions/Operators" for a detailed discussion.
The following examples show queries that benefit from the VARCHAR2 semantics on CLOBs. In prior releases, the effects of these queries used to be achieved, in PL/SQL code, using DBMS_LOB calls. It will be convenient for you to be able to use the same interface as VARCHAR2s to access data.
|
Note: These examples are based on the following revised version of the Multimedia application schema described in Appendix B, "The Multimedia Schema" and Chapter 10, "Internal Persistent LOBs" under "Creating a Table Containing One or More LOB Columns":
|
SELECT Gist||Story FROM Multimedia_tab WHERE Story LIKE Gist; SELECT SUBSTR(Story, 20, 1), LENGTH(Story) FROM Multimedia_tab WHERE Gist NOT IN Story; -- A temp LOB is created and returned for 'Gist||Story' and 'SUBSTR(Story,20,1)' because story is a CLOB.
In PL/SQL, a number of semantic changes have been made as described in the previous paragraphs.
|
Note: The following discussions, concerning CLOBs and VARCHAR2s, also apply to BLOBs and RAWs, unless otherwise noted. In the text, BLOB and RAW are not explicitly mentioned. |
The new PL/SQL semantics support is described in the following sections as follows:
The implicit conversion in both directions, from CLOB to VARCHAR2, and from VARCHAR2 to CLOB, have made the following operations between CLOBs and VARCHAR2s possible:
The following example illustrates the way CLOB data was accessed prior to this release. This application tries to simply display both the Gist and Story from the table Multimedia_tab.
declare myStoryLOB CLOB; myStoryBuf VARCHAR2(4001); amt NUMBER:=4001; offset NUMBER := 1; begin SELECT Story INTO myStoryLOB FROM Multimedia_tab WHERE Clip_ID = 10; DBMS_LOB.READ(myStoryLOB, amt, offset, myStoryBuf); -- Display Gist and Story by printing 'myStoryBuf'. end;
The following example illustrates the way CLOB data is accessed with this release when the CLOBs are treated as VARCHAR2s:
declare myStoryBuf VARCHAR2(4001); begin SELECT Story INTO myStoryBuf FROM Multimedia_tab WHERE Clip_ID = 10; -- Display Story by printing myStoryBuf directly end;
declare myGistLOB CLOB;begin
SELECT Gist INTO myGistLOB FROM Multimedia_tab WHERE Clip_ID = 10; -- myGistLOB is a temporary LOB. -- Use myGistLOB as a lob locatorend;
In SQL and PL/SQL, the following new explicit conversion functions have been added to convert other data types to CLOB, NCLOB, and BLOB as part of the LONG-to-LOB migration:
Other explicit conversion functions are not supported in this release, such as, TO_NUMBER(), see Table 7-6. Conversion function details are explained in Chapter 8, "Migrating From LONGs to LOBs".
CLOB and VARCHAR2 are still two distinct types. But depending on the usage, a CLOB can be passed to SQL and PL/SQL VARCHAR2 built-in functions, behaving exactly like a VARCHAR2. Or the variable can be passed into DBMS_LOB APIs, acting like a LOB locator. Please see the following combined example,"PL/SQL Example 4: CLOB Variables in PL/SQL".
PL/SQL VARCHAR2 functions/operators need to take CLOBs as argument or operands.
When the size of a VARCHAR2 variable is not large enough to contain the result from a function that returns a CLOB, or a SELECT on a CLOB column, an error should be raised and no operation will be performed. This is consistent with current VARCHAR2 behavior.
1 declare 2 myStory CLOB; 3 revisedStory CLOB; 4 myGist VARCHAR2(100); 5 revisedGist VARCHAR2(100); 6 begin 7 -- select a CLOB column into a CLOB variable 8 SELECT Story INTO myStory FROM Multimedia_tab WHERE clip_id=10; 9 -- perform VARCHAR2 operations on a CLOB variable 10 revisedStory := UPPER(SUBSTR(myStory, 100, 1)); 11 -- revisedStory is a temporary LOB 12 -- Concat a VARCHAR2 at the end of a CLOB 13 revisedStory := revisedStory || myGist; 14 -- The following statement will raise an error since myStory is 15 -- longer than 100 bytes 16 myGist := myStory; 17 end;
Please note that in line 10 of "PL/SQL Example 4: CLOB Variables in PL/SQL", a temporary CLOB is implicitly created and is pointed to by the revisedStory CLOB locator. In the current interface the line can be expanded as:
buffer VARCHAR2(32000) DBMS_LOB.CREATETEMPORARY(revisedStory); buffer := UPPER(DBMS_LOB.SUBSTR(myStory,100,1)); DBMS_LOB.WRITE(revisedStory,length(buffer),1, buffer);
In line 13, myGist is appended to the end of the temporary LOB, which has the same effect of:
DBMS_LOB.WRITEAPPEND(revisedStory, myGist, length(myGist));
In some occasions, implicitly created temporary LOBs in PL/SQL statements can change the representation of LOB locators previously defined. Consider the next example.
1 declare 2 myStory CLOB; 3 amt number:=100; 4 buffer VARCHAR2(100):='some data'; 5 begin 6 -- select a CLOB column into a CLOB variable 7 S ELECT Story INTO myStory FROM Multimedia_tab WHERE clip_id=10; 8 DBMS_LOB.WRITE(myStory, amt, 1, buf); 9 -- write to the persistent LOB in the table 10 11 myStory:= UPPER(SUBSTR(myStory, 100, 1)); 12 -- perform VARCHAR2 operations on a CLOB variable, temporary LOB created. Changes 13 -- will not be reflected in the database table from this point on. 14 15 update Multimedia_tab set Story = myStory WHERE clip_id = 10; 16 -- an update is necessary to synchronize the data in the table. 17 end;
After line 7, myStory represents a persistent LOB in Multimedia_tab.
The DBMS_LOB.WRITE() call in line 8 directly writes the data to the table.
No UPDATE statement is necessary. Subsequently in line 11, a temporary LOB is created and assigned to myStory because myStory should now behave like a local VARCHAR2 variable. The LOB locator myStory now points to the newly-created temporary LOB.
Therefore, modifications to myStory will no longer be reflected in the database. To propagate the changes to the database table, an UPDATE statement becomes necessary now. Note again that for the previous persistent LOB, the UPDATE is not required.
Temporary LOBs created in a program block as a result of a SELECT or an assignment are freed automatically at the end of the PL/SQL block/function/procedure. You can choose to free the temporary LOBs to reclaim system resources and temporary tablespace by calling DBMS_LOB.FREETEMPORARY() on the CLOB variable.
declare Story1 CLOB; Story2 CLOB; StoryCombined CLOB; StoryLower CLOB; begin SELECT Story INTO Story1 FROM Multimedia_tab WHERE Clip_ID = 1; SELECT Story INTO Story2 FROM Multimedia_tab WHERE Clip_ID = 2; StoryCombined := Story1 || Story2; -- StoryCombined is a temporary LOB -- Free the StoryCombined manually to free up space taken DBMS_LOB.FREETEMPORARY(StoryCombined); StoryLower := LOWER(Story1) || LOWER(Story2); end; -- At the end of block, StoryLower is freed.
Like VARCHAR2s, when a CLOB is compared with another CLOB or compared with a VARCHAR2, a set of rules determines the comparison. The rules are usually called a "collating sequence". In Oracle, CHARs and VARCHAR2s have slightly different sequences due to the blank padding of CHARs.
As a rule, CLOBs follow the same collating sequence as VARCHAR2s. That is, when a CLOB is compared, the result is consistent with if the CLOB data content is retrieved into a VARCHAR2 buffer and the VARCHAR2 is compared. The rule applies to all cases including comparisons between CLOB and CLOB, CLOB and VARCHAR2, and CLOB and CHAR.
It makes no sense to compare CLOBs with non-character data, or with BLOBs. An error is returned in these cases.
The OCI and Java interfaces now provide the ability to bind and define VARCHAR2 variables to SQL and PL/SQL statements with LOBs.
This release does not provide the ability to define variables in the other direction, that is, defining a client-side CLOB locator on a VARCHAR2 column.
Be aware of the following performance issues, when using SQL semantics with LOBs.
In Oracle9i, the maximum length restriction for all column data and buffer size when processing SQL queries, can be more than 4K bytes. You can process LOB data, which can be as long as 4G bytes, in SQL!
Temporary LOBs are used internally if the data is greater than 4K bytes to store intermediate results.
For large VARCHARs, SQL queries now perform in a similar fashion to when accessing CLOBs through the previous set of LOB APIs.
In PL/SQL, C (OCI), and Java, SQL query results return temporary LOBs for operation/function calls on LOB columns. For example:
SELECT substr(CLOB_Column, 4001, 32000) FROM ...
Returned temporary LOBs automatically get freed at the end of a PL/SQL program block.
You can choose to free any unneeded temporary LOBs at any time to free up system resources and temporary tablespace. Without proper deallocation of the temporary LOBs returned from SQL queries, temporary tablespace gets filled up steadily and you could observe performance degradation. See "PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually", for an example of freeing temporary LOBs explicitly.
The performance of an SQL query execution on CLOB columns should be compared to that of a query on VARCHAR2s or LONGs of the same size. Expect the performance on LOBs to be within 80% of VARCHAR2s/LONGs or better.
User-defined aggregates (UDAGs) provide a mechanism for application/cartridge developers, and end-users to implement and deploy new aggregate functions over scalar data types (including LOBs) as well as object and opaque types.
The following are two examples of applications for user-defined aggregates (UDAGs):
For example, a query to find the state boundary by unionizing the county boundaries in each state can be executed as follows:
SELECT SDO_AGGR_UNION(county.geometry) FROM COUNTIES GROUP BY county.state;
SELECT group_column, GLB(rowlabel) FROM x GROUP BY group_column.
User Defined Aggregate functions (UDAG) refer to aggregate functions with user specified aggregation semantics. You can create a new aggregate function and provide the aggregation logic using a set of routines. Once created, user defined aggregate functions can be used in SQL DML statements like built-in aggregates.
Complex data is typically stored in the database using object types, opaque types or LOBs. User-defined aggregates are useful in specifying aggregation over these domains of data.
UDAGs can also be used to create new aggregate functions over traditional scalar data types for financial or scientific applications. Since, it is not possible to provide native support for all forms of aggregates, this functionality provides you with a flexible way to add new aggregate functions.
An aggregate function takes a set of values as input and returns a single value. The sets of values for aggregation are typically identified using a GROUP BY clause. For example:
SELECT AVG(T.Sales) FROM AnnualSales T GROUP BY T.State
UDAGs allow you to register new aggregate functions by providing specific (new) implementations for the preceding primitive operations.
User-defined aggregate functions have the following DDL support:
User-defined aggregate functions have the following DML and query support:
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|