Solved

CLOB in an table

Posted on 2014-09-21
6
497 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 94
Wrap Oraccle SQL*Plus executable Command 4 69
Query Records that don't match 8 34
MULTIPLE DATE QUERY 15 52
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 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

13 Experts available now in Live!

Get 1:1 Help Now