CLOBS vs BLOBs vs VARCHAR2
564801Mar 29 2007 — edited Mar 31 2007Help please.
I have a customer who "heard" that using clobs was extremely inefficient/slow.
(More managerial than techie)
Here is the scenario:
A. This is NOT a high volume transaction application.
(so I would assume the difference in performance would have to be huge to have a huge effect.)
B. Comments and text can vary wildly in size - albiet most are under 4,000 character. Most of this data was in multiple rows (with line nbrs) VARHCAR2(2000) in the old system.
C. Another firm wants them to dump the text - all of it - if it exceeds 4,000 characters (most of their cmt/text fields are VARCHAR2(4000)) - into BLOBS.
(IMO- with what little I know I don't see how putting text in BLOBS is preferrable to CLOBS.)
D. I have pointed out that having this text partially in VARCHAR2(4000) and partially in BLOBS - could have a number of implications - like what happens when the text has 3999 characters and a user needs to update it.
E. The end users and data miners want to be able to search the data/text.
F. There are some strong negative opinions on the need for more than 4000 characters of text - however, since there can be some complicated situations there are tens of thousands cases where the data already is longer than 4,000 characters.
G. Because going forward they want to limit the length of the text to under 4,000 they don't want to change the app to continue with the approach of multiple line (which is what the existing app does.)
Advice, arguments, and opions would be appreciated.