Solved

CLOB in an table

Posted on 2014-09-21
6
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 74

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 77

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

636 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