db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: What happens during a database upgrade?
Date Fri, 29 May 2009 07:59:44 GMT
Knut Anders Hatlen wrote:
> David Goulden <relic@prepee.com> writes:
> 
>> For these tables we don't use ORDER BY, so hopefully the bug you mentioned
>> won't affect us. Out of curiosity, is there any advantage to doing a hard
>> upgrade? Except for the CLOB performance issue, we're quite happy with
>> version 10.4.
> 
> Hi David,
> 
> Off the top of my head, I think the advantages you'll get from a hard
> upgrade 10.4 -> 10.5 are faster length calculation for CLOBs and one new
> system procedure (SYSCS_UTIL.SYSCS_UPDATE_STATISTICS).
> 

Again, this is mostly correct.

For existing CLOBs you won't get the faster length calculation, even if 
you do a hard upgrade. CLOBs inserted into a 10.5 database using the 
JDBC methods that take a length argument will benefit. If you use the 
"length less" JDBC overrides, than you're back to the 10.4 situation 
when it comes to the improved length calculation.

Now, we don't really have a good way to upgrade the format of existing 
CLOBs yet. One possibility is to run this query:
     UPDATE myTable SET clobValue = clobValue || '' [WHERE 
length(clobValue) < someThreshold]
This will upgrade the format by concatenating the existing CLOB with the 
empty string. However, the concatenation will materialize the CLOB, so 
if you have large CLOBs you need to have a lot of memory in the server 
(2 GB++).

I have been looking into using a function that just returns the same 
CLOB passed in to it, but Derby doesn't support this yet. The advantage 
of that approach is that the CLOB hopefully won't be materialized. The 
implementation of this functionality has been started under DERBY-4066.


Regards,
-- 
Kristian

Mime
View raw message