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
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
Comment