What is the Difference of CLOB and BLOB in Oracle?

Would you explain the difference of data types between CLOB and BLOB in Oracle? This is a quite common question in Oracle DBA/developer interview. There is usually at least one question regarding data types in a database position interview. To answer this question, you need to explain what is LOB, the difference between CLOB and BLOB, and the similarities as well.

Sample answer:

"In newer version (since 9i) Oracle introduced large object (LOB) data types to handle complex structured and unstructured data. CLOB is the the character LOB and BLOB is the binary LOB. The CLOB datatype stores character set data in the database. A specific CLOB, called NCLOB, stores Unicode national character set data. Storing varying-width LOB data in a fixed-width Unicode character set internally enables Oracle database to provide efficient character-based random access on CLOBs and NCLOBs. The BLOB datatype stores unstructured binary data in the database. BLOB primarily is used to hold non-traditional data, such as images, documents (pdf, doc, txt, etc.), and media files like audio and video. Both CLOB and BLOB participate fully in transactions. Changes made to a CLOB or BLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB and BLOB locators cannot span transactions or sessions. Both CLOB and BLOB can store huge data, up to 128 terabytes."

A follow-up question may ask for the size limitation of the data type. Answer is given above.

An alternative question may ask by a case scenario: we want to store pdf files in Oracle database, what data type should we use? The answer is; BLOB.

What is the difference of CLOB and LONG?

A question may also ask to address the advantage of CLOB over the traditional LONG data type. The following points outline the differences of LOB and LONG:

  • A table can contain multiple LOB columns but only one LONG column.

  • A table containing one or more LOB columns can be partitioned, but a table containing a LONG column cannot be partitioned.

  • The maximum size of a LOB is 128 terabytes depending on database block size, and the maximum size of a LONG is only 2 gigabytes.

  • LOBs support random access to data, but LONGs support only sequential access.

  • LOB datatypes (except NCLOB) can be attributes of a user-defined object type but LONG datatypes cannot.

  • Temporary LOBs that act like local variables can be used to perform transformations on LOB data. Temporary internal LOBs (BLOBs, CLOBs, and NCLOBs) are created in a temporary tablespace and are independent of tables. For LONG datatypes, however, no temporary structures are available.

  • Tables with LOB columns can be replicated, but tables with LONG columns cannot.

In Oracle, there are 4 LOB data types: BLOB, CLOB, NCLOB, and BFILE. The LOBs enable users to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format. They provide efficient, random, piece-wise access to the data. Oracle recommends that you always use LOB datatypes over LONG datatypes. You can perform parallel queries (but not parallel DML or DDL) on LOB columns. Other database systems, such as DB2, also have similar data types.

No comments:

Post a Comment