Hi all,
I have a tabla with a CLOB column in it and I need to be able to retrieve this clob value, along with others, using
JDBC and create an XML. I did a performance
test with this and found that just to read through a result set of 900 rows with one CLOB column in each, is taking 7 seconds.
My query goes like this
But if I change the query to
(** I know for this set of where clauses the size of the clob wont be too large that it can't fit in CHAR).
the time is down to one second or less. So the colb actually returns a locater and then JDBC will read the actual data when I call read on that column, resulting in 900 read operations taking too much time. Having the data converted to CHAR right in the SQL gives me great performance benefits. But I can not do it all the time as in some cases the value might not fit in CHAR and Oracle throws an exception.
Is there a way that I can read the actual data instead of retrieving a pointer to the CLOB data? Or is there any way of reading these CLOBS faster? I tried calling just rs.getString() (works with 10g & above) and also tried with
and read a char stream. Both take some 6 to 7 seconds to loop through 900 records.
I would be thankful if some one who had seen this before cold suggest a better way or point me to right resources.
Thank you
Raj.
