I have an table comments , Our application team have decided to modify one of the column as CLOB , as we need to store more than 4000 characters . I have been asked to do the impact analysis , since Iam new to CLOB , need help on the below points
The table structure is
COMMENT_ID NUMBER Primary KEY
COMMENTS_DESC VARCHAR2(40000) -- WILL BE CHANGED TO CLOB
We have an unique Index in Comment_id, non -unique index in subject and comments_Desc columns
1. Can we have an mutilple CLOBS colums in an table.
2. It may happen that we may stores less than 4000 characters also in the column , in that case how does oracle store the CLOB data in DB.
3. I need to define an index on this column , I need to understand the impact of this indexing on the column when compared to performance under these operations
a) Use of Like operator
b) use of column in the where clause
4. Impact of indexes on the CLOB column and other columns in oracle.
5. DML operations on the CLOB column.
Any guidance is really appreciated.