CLOB in an table

Hi ,

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
COMMENTS
COMMENT_ID NUMBER Primary KEY
SUBJECT    VARCHAR2(4000)
COMMENTS_DESC VARCHAR2(40000) -- WILL BE CHANGED TO CLOB
CREATED_BY NUMBER
CREATED_DATE DATE


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.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jsaunCommented:
It used to be the case that every row for a CLOB took up a certain minimum amount of space, so if you put CLOBs inline, even if it was NULL, it would take up a bunch of space.  Not sure if that's still true.  If you expect a lot of NULLs anyway, you might want to break out those columns to a separate table with a foreign key and the CLOB.  Then you only insert there if your input actually contains an object.

There are CLOB function tools for searching and indexing, but be wary of performance.  
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5533095920114
0
PortletPaulfreelancerCommented:
LOB Storage
http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#i1012988

1. Can we have an mutilple CLOBS colums in an table.
YES
 "You can store multiple LOBs in a single row" (above reference)
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.
"For inline LOBs, the database will store LOBs that are less than approximately 4000 bytes of data in the table column."
(above reference)
3. I need to define an index on this column
see heading "Indexing LOB Columns"
4. Impact of indexes on the CLOB column and other columns in oracle.
not sure what you want here
5. DML operations on the CLOB column.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wasim Akram ShaikCommented:
4. Impact of indexes on the CLOB column and other columns in oracle.

There is no dependency on Indexed columns lobs with the rest of the columns indexes in oracle database, Indexed lob columns are treated as a separate entity by oracle.

However, there are few more considerations and recommendations from Oracle while using LOB columns which are stated in a white paper, you can review them here. Rest points are already well covered by Paul

http://www.oracle.com/technetwork/articles/lob-performance-guidelines-128437.pdf
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
if you upgrade to 12c you can turn on support for varchar2 up to 32K bytes (SQL and PL/SQL limits would be the same)

the extra size is maintained in lob segments but external functionality is still supported.  i.e. you can index a large varchar2 but not a clob.

however - there are still large index limits imposed by block size
0
slightwv (䄆 Netminder) Commented:
It is referenced in the first post and in your other question but Oracle Text is probably still your best bet using wildcards on a text column.

In 11g and above, I would encourage you to look at SECUREFILE CLOBs:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#SQLRF54539

You can COMPRESS them.

Depending on your specific requirements and the average size of the CLOB, you might be able to get a LOT of your CLOB data stored inline and not see much of a performance hit and wasted space.

By wasted space, when stored outside the main table( not inline):
A CLOB, no matter how small will take at least one entire block in the tablespace.
So with an 8K block size in the database and 1 million CLOBs with only 1 character will allocate 8Gig of space.
0
sam_2012Author Commented:
thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.