Re: Underlying Implementation of VARCHAR data types

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 08 Mar 2004 21:10:58 -0800
Message-ID: <1078809023.403892@yasure>

Yong Huang wrote:

> mikelbell2000_at_yahoo.com (Mike L. Bell) wrote in message news:<49d7474a.0403080855.70089aa7_at_posting.google.com>...
>

>>...
>>Also, in Oracle do I not have to worry about performance penalties
>>associated with VARCHAR types in other systems with things like page
>>overflows if the resultant update of the field results in a length
>>that will no longer fit on the same data page?

>
>
> We often see debate on CHAR vs VARCHAR2 (when the column is known to
> contain fixed length strings). I don't recall seeing anybody's
> performance test. If there is one, the difference may be barely
> noticeable.
>
> 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.
>
> Yong Huang

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.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Mar 08 2004 - 23:10:58 CST