Oracle Database Discussions

Hi,

I have multiple VARCHAR2(4000) columns in my Oracle 10g database. Application users usually enter notes, descriptions, and other text into these fields. Very rarely does the data ever get to 4000 characters, but it has happened on rare occurrences... maybe one or two times a year. So, I am considering changing to a CLOB.

I am primarily concerned with data retrieval performance through ad hoc queries in the application. If the CLOB is going to be slower to retrieve and present data to users in the application, I would rather stick with the VARCHAR2 field. If users absolutely must insert data over 4000 characters, I still offer them the ability to associate that data with the record using file attachments stored outside the db.

Also, what about the difference in querying on the specific column... can a CLOB field even be queried on? Could I find a specific word in the field, and if so, is there a performance impact?

I basically just want to know if I should change these columns to CLOB so that users can enter however much they want to, or since it rarely happens, should I leave it at VARCHAR2 and restrict users to the maximum 4000 characters for better application performance.

Thanks for the advice!