What are LOBs all about?

Posted 7 April, 2006 - 13:41

ORACLE 8 saw the introduction of LOBs - large objects. They support unstructured data such as files, media content, documents and anything else you want to stuff inside a LOB. Back in ORACLE 7, LOBs did not exist and their counterpart type was the LONG and LONG RAW data types. These types are still available but LOBs are gaining a greater and greater advantage over their elder cousins as time goes by.

For a start,

  • LOBs can hold up to 4Gb of data from ORACLE 8i and beyond. It is claimed that Oracle 10g CLOBs are capable of holding up to 128Tb, depending on the database block size and chunk setting;
  • LONGs can only manage up to 2Gb of data;
  • A table can host multiple LOB columns;
  • A table can host only a single LONG column at most from ORACLE 7.3 and later;
  • LOBs support random access to data;
  • LONGs support only sequential access to data;
  • It is possible to build function-based indexes on LOB columns

There are currently four types of LOB datatype:

  1. BLOB - binary large object
  2. CLOB - character large object
  3. NCLOB - national character lob
  4. BFILE - large objects stored as operating system files

Accessing LOBs

From ORACLE 9i and beyond, LOBs are accessible in much the same way as VARCHAR2 columns if their size is relatively small (<100Kb). Beyond that, they are manipulated via the DBMS_LOB package.

There are gotchas, though. One is that LOBs cannot appear in a SELECT list that contains the DISTINCT operator as they cannot be directly compared to each other.