Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CLOBS vs BLOBs vs VARCHAR2

564801Mar 29 2007 — edited Mar 31 2007
Help 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 28 2007
Added on Mar 29 2007
7 comments
43,250 views