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

[Mat Hess]

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

[DA Morgan]

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.wash ington.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 =---