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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 17 Jan 2005 14:12:59 -0800
Message-ID: <41ec385e$1_1_at_127.0.0.1>


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
damorgan_at_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 =---
Received on Mon Jan 17 2005 - 23:12:59 CET

Original text of this message