Re: Underlying Implementation of VARCHAR data types

On Mon, 08 Mar 2004 21:10:58 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>> But I remember a few years ago I looked at the location of data inside
>> a data block and found that updating 'NY' to 'TX' or even to 'NY'
>> could change the data location in the block if the column is VARCHAR2.
>> If it's CHAR, it stays in the same location. I could remember wrong
>> but if that's the case, it may have performance implication.
>
>Only if you miscalculate PCTFREE and PCTUSED or, like most people, just
>go with Oracle's defaults values and apply no synapses to the problem.
>But then the same could also be caused by updating a DATE column too
>so there really is not point to the argument.

 Could you give an example of this? I was under the impression that dates were a fixed-length datatype, always 7 bytes. The only things I can think of are a date being updated from NULL to not-null, or it being part of a partition on that date with row movement enabled and the change in value causes it to be moved to another partition? If you'd said NUMBER then I could understand that, since it's a variable length datatype.

 Or can updates that don't alter the length of a row actually cause the row to move within a block, e.g. when updating a not-null DATE to a different value in a plain heap-organised table?

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>