Hi all,
We are inserting data that can be between 0 and 8000 characters long in a 10.2.0.4 database. A .net process calls a web service, parses the results and calls a package procedures that does a row-by-row insert, like this (simplified):
procedure mytable_ins(
p_id in mytable.id%type,
p_data in mytable.data%type
) is
begin
insert into mytable
(id, data)
values
(p_id, p_data);
end;
If the data field in mytable is a varchar2(4000) the proces is fast enough, but when we change the datatype to clob, the perfomance drops to an unacceptable level (about 100 times slower).
This is the case even though the actual data is less than 4000 characters and the clob is stored inline.
The bulk of the data will be less than 4000 chars, but we have to account for cases where the data is up to 8000. Of course we could work around the issue and store it as two varchar2 fields, but this would be bad design imo.
What possibilities are there to efficiently store clobs, when you know that most of the data is < 4000 chars?
Thanks!
Rob