By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,230 Members | 2,436 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,230 IT Pros & Developers. It's quick & easy.

CLOB: Why are they so slow (compared with VARCHAR2)

P: n/a
We are currently developing a new application. In this application, we
have a table which will hold a large number of rows, where many text
fields (one text field per row) will be stored. The users would prefer
to have an unlimited field size (in spite of the fact that most of all
fields have much less than 4000 characters), so we chose CLOB as the
datatype of the corresponding column.

We experienced very poor performance when we were using CLOB field,
compared with VARCHAR2. Please try the following:

create table test (test_text clob)
/
insert into test values ('this is just a test')
-- REPEAT THE INSERT 2000 TIMES
/

In my Database, a SELECT * FROM test takes 0.890 seconds.
If I run the same script, but use VARCHAR2(4000) for the test_text
column, then the SELECT takes only 0.031 seconds.

My Questions:
1. Why are CLOBS so slow ?
2. Is there a way to improve the performance of the CLOB Fields ?

Kind Regards
Mat Hess
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Mat Hess wrote:
We are currently developing a new application. In this application, we
have a table which will hold a large number of rows, where many text
fields (one text field per row) will be stored. The users would prefer
to have an unlimited field size (in spite of the fact that most of all
fields have much less than 4000 characters), so we chose CLOB as the
datatype of the corresponding column.

We experienced very poor performance when we were using CLOB field,
compared with VARCHAR2. Please try the following:

create table test (test_text clob)
/
insert into test values ('this is just a test')
-- REPEAT THE INSERT 2000 TIMES
/

In my Database, a SELECT * FROM test takes 0.890 seconds.
If I run the same script, but use VARCHAR2(4000) for the test_text
column, then the SELECT takes only 0.031 seconds.

My Questions:
1. Why are CLOBS so slow ?
2. Is there a way to improve the performance of the CLOB Fields ?

Kind Regards
Mat Hess


And your Oracle version is?
And running StatsPack and looking at waits reveals?
And the difference in speed is a real or theoretical problem?
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.