Why are CLOBs so slow, compared with VARCHAR2

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 Received on Mon Jan 17 2005 - 03:31:22 CST