Oracle tip - using CLOB columns

Recently I had to investigate about the usability of the CLOB (Character Large OBjects) columns in oracle database tables. Actually the requirement was to store a ralatively larger string values into the table column. I discuss my experience with the task here.

When creating a LOB (either CLOB or BLOB) column, oracle offers 2 ways of storage to choose from.

1. In-line storage
   Data is stored within the table row. For the values which are greater than 3964 (from reference) bytes, oracle stores the data in a different place (most probably a separate table space) and keeps a pointer to that location within the row.
   The clause ENABLE IN ROW is used to select this option.
   
   
2. Out-of-line storage
   Data is always stored outside and the location pointer is stored in the table row.
   DISABLE IN ROW is used to choose this option in the storage clause.
   

Both options have pros and cons. I had looked at them in different aspects to choose the right one for my purpose.

Performance: Option 1 will give better response time when saving and retrieving a row. This can be even better when saving a value of size lesser than the maximum limit for in-line. So, if the application gives more important to the performance, it should be option 1.

Storage: Amount of space occupied by the same value in both options are different. I got to know this through experience. Option 2 uses more space than it is required for option 1. This implies, if the application's database can be allocated large storage space, going for option 2 should not be a problem.

There are other storage parameters like CHUNK SIZE which affect the performance and storage. But, I don't want to discuss that here. Oracle reference can give good knowledge on that.

In my situation, I had to choose a method where performance is more important and the space usage is minimal. The avarage size of the sample values was also a factor, where it was almost 4000 bytes. So, I decided to go for option 1, which seemed to be the most appropriate one.

However I want to say one experience based fact here. When VARCHAR2 and CLOB (in-line) are used to store the same column value in a table, the later occupies much more physical storage space than the former (even double). We shouldn't just go for CLOB since it offers more room. In conclusion I would say, VARCHAR2 should be considered first in every angle before thinking about CLOBs.