Oracle BLOB vs VARCHAR Oracle BLOB vs VARCHAR oracle oracle

Oracle BLOB vs VARCHAR


Another option is CLOB. For text data it's more logical to use CLOB than BLOB. Even if you don't have to analyze the data within the database it might still make sense to use CLOB, because even viewing the data is easier.

Some features are only available using VARCHAR. For example, you can only create an index on VARCHAR columns (I'm not talking about fulltext index here, I know you can create a fulltext index on a CLOB column). You can't have a CLOB or BLOB primary key (I guess you don't need that; just as an example).

Most VARCHAR operations are much faster than CLOB / BLOB operations. Even reading data is faster if you use VARCHAR (unless there is really a lot of text in the column). VARCHAR needs less memory overhead, but they will usually be fully read in memory, so at the end VARCHAR might still use more memory.


If you're going to store text data that can't fit in a VarChar2 then I think you're supposed to use a CLOB.

Quote from OraFaq: *A CLOB (Character Large Object) is an Oracle data type that can hold up to 4 GB of data. CLOB's are handy for storing text. *


Short strings → VARCHAR

Long strings → CLOB

Binary data → BLOB