RE: Porting Guide and Statement made about Char Vs VarChar

[vhamberg@xxxxxxxxxxx]

Walden

Last time I actually looked at the data space for a physical file with VARCHARs, the space used for such a column is not recovered when you change the value, whether shorter or longer. Longer makes a little sense, but it would seem a shorter value could be placed in the old spot - this is a hazy memory for that, however.

It also seems to me that one of CPYF and RGZPFM does not recover that space - but that is a REALLY hazy memory. 2001 is ancient history, and maybe things have changed. Although the overhead to reuse auxiliary space chunks could be considerable.

This thought was triggered by your 25->31 byte change comment - although perhaps not directly related.

Cheers
Vern 
 -------------- Original message ----------------------
From: "Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>
> The use of the term "predictive" was perhaps a mistake. Agreed, the row
> was accessed, the predictive part is whether I'm going to access the LOB
> parts of the row. In many cases I will access the LOB, but in many cases
> I will not. True the same can be said of any field in any row, (I may
> need AddressLine2, I may only need City) but to me the difference comes
> from the "LARGE" part of the Large Object definition. 
>
> To me (and perhaps I'm nuts) I don't expect the access time for a 30
> byte address column to be any different than the access time for a 50
> byte address column. However, I do expect the access time for the
> address column (30 or 50 bytes) to be substantially different than the
> access time for a 20MB jpg image column. Or to be more specific, I'd
> love for there to be _no_ different, however, I'm not willing to give up
> any performance in the access time for the address column to have a
> faster access to the image column. Common sense says the processes we'll
> perform on LOBs are (relatively) long-running anyway, so taking the hit
> of an extra IO isn't a big deal. 
>
> This is especially true given that paging in the LOB (which I may not
> use) is so expensive in terms of its effect on the row cache in that
> memory pool. Heck, a good JPG image could wipe out a normal memory pool.
> :) Looking at the performance across the entire machine I can't imagine
> the benefit of one fewer IO for a LOB retrieval is worth the cost of
> such a cache purge.
>
> I do agree that my trivial example of a 25 to 31 byte change is unlikely
> to cause a complete row copy due to the slush-space on a page, but take
> the more likely example of a varchar for notes about an image, a
> thumbnail LOB (100K max) and a jpg LOB (500Mb Max?). Updating the notes
> on an image (or adding them in the first place, or adding the thumbnail
> in the first place) is going to be a _very_ costly exercise.
>
> -Walden