Comparison between LOBs, and LONG & LONG Raw Datatypes

출처 :  https://support.oracle.com



Purpose

This bulletin compares LOBs with LONG and LONG Raw datatypes, and lists the restrictions for using LOBs.
 


Scope & Application

Information for DBAs and Application Developers.



Comparison between LOBs, and LOBs, and LONG & LONG Raw Datatypes


LOBs (Large Objects) are similar to the LONG and LONG RAW datatypes,
but they have some differences:

1. Multiple LOBs can be stored in a single row whereas only one single LONG or LONG raw can be stored per row.

2. A LOB can be an attribute of a user-defined datatype whereas a LONG or LONG RAW cannot.

3. In the case of a LONG or LONG RAW the entire value is stored in the table column whereas for LOBS, 
   only the LOB locator is stored in the table column.  BLOB and CLOB (Internal LOBs) data can be stored in 
   separate tablespaces and BFILE (External LOB) data is stored as an external file. So, when we access
   a LOB only the locator is returned, but when we access a LONG or LONG RAW the entire value is returned.

4. A LOB can be up to 4 gigabytes in size. The BFILE maximum is operating system dependent, but cannot
   exceed 4 gigabytes. The valid accessible range is 1 to (232-1), whereas the limit is only 2 gigabytes for
   LONG or LONG RAW datatypes.

5. There is greater flexibility in manipulating data in a random, piece-wise manner with LOBs than there is
   with LONG or LONG RAW data.

6. LOBs can be accessed at random offsets while LONGs must be accessed from the beginning to the 
   desired location.

7. You can replicate LOBs in both local and distributed environments wheresas with LONG and LONG raw,
   this cannot be done.

8. Existing LONG columns can be converted to LOBs using the TO_LOB() function whereas conversion of
   LOBS to LONG or LONG Raw is not supported.



There are some restrictions on using LOBs. 
Be aware of the following:

1. LOBs are not allowed in clustered tables and thus cannot be a cluster key.

2. LOBs are not allowed in GROUP BY, ORDER BY, SELECT DISTINCT, aggregates and  JOINS. 
   However, UNION ALL is allowed on tables with LOBs. UNION, MINUS, and SELECT DISTINCT are allowed on
   LOB attributes if the object type has a MAP or ORDER function.

3. LOBs are not analyzed in ANALYZE... COMPUTE/ESTIMATE STATISTICS statements.

4. LOBs are not allowed in partitioned index organized tables but are allowed in non-partitioned index organized tables.
5. LOBs are not allowed in VARRAYs.

6. NCLOBs are not allowed as attributes in object types but NCLOB parameters are allowed in methods.



RELATED DOCUMENTS
-----------------------

Oracle8i Application Developer's Guide