Solved

CLOB in an table

Posted on 2014-09-21
6
488 Views
Last Modified: 2014-09-24
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.
0
Comment
Question by:sam_2012
6 Comments
 
LVL 1

Expert Comment

by:jsaun
ID: 40335969
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40335977
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40336027
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40336558
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40336603
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
 

Author Closing Comment

by:sam_2012
ID: 40342146
thanks a lot
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now