Re: Get max length of data for a column

Hi

One of the big reasons that I know of to use CHAR instead of VARCHAR2 is row chaining. This is when a row gets moved from one block to another because there isd no longer room in the block for the row. How does this happen? If you use CHAR but when you insert the row the CHAR field does not have a value then that row is stored as a null (ie takes up no space). Later when you update the field, the length expands from null to whatever the value is for CHAR. This makes the row length increase and if there is not enough room in the block, the row gets moved. However when moving the row Oracle does not update indexes, etc and so a pointer is left in the orginal block. This means that a rerad for this row now consumes two blocks. So, as an example, if you are having to store addresses, then the 
 zip code would be a good candidate for a CHAR field as most US addresses would inlude the zip code, the catch here would be on the initial insert put something into the field (a space of a 0) so that the room is already taken.

Cheers

Peter
--- In [email protected], Martin Gainty <mgainty@...> wrote:
>
> 
> Morning All-
> 
> I *thought* VARCHAR2 was varying size based on a Run Length Encoding for the column
> where CHAR was always fixed length 
> 
> to acquire column length the path of least resistance is to take the length from USER_TAB_COLUMNS e.g.
> SQL> select DATA_LENGTH from USER_TAB_COLUMNS where table_name='USER$' AND COLUMN_NAME='PASSWORD';
> 
> Norm or Bert does this conform to your understanding?
> 
> Cheers,
> Martin Gainty 
> ______________________________________________ 
> GMT+5 (this week)
> 
> 
> To: [email protected]
> From: toad@...
> Date: Wed, 18 Apr 2012 08:26:05 +0100
> Subject: Re: [toad] RE: Get max length of data for a column
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>  
> 
> 
> 
>   
> 
> 
>     
>       
>       
>       Morning Mike,
> 
> 
> 
> > Thanks for that clarification. To be honest, we don't really use CHAR here
> 
> > anymore, which is why I've either forgotten that important piece of
> 
> > information, or (worse) never knew it.
> 
> Years ago, I wrote a "data obfuscation" routine with simply replaced 
> 
> certain columns with random data generated by DBMS_RANDOM. All my CHAR 
> 
> columns ended up being exactly the defined length! That's when I noticed 
> 
> it. I ended up using VSIZE() to get the length of the TRIM()med CHAR 
> 
> columns. Sort of stuck in my "cache" since then.
> 
> 
> 
> > Out of interest, is there any reason
> 
> > why one would want to use CHAR rather than VARCHAR?
> 
> I'm not sure. I use them occasionally myself. Just for internal space 
> 
> saving. A VARCHAR2 has a hidden size setting. If the column is 100 long 
> 
> and you store 40 in it, there's a counter internally that says "this is 
> 
> how long the data is". Not a great reason, but if you have flag columns 
> 
> - gender, for example, that are only one character long, you don't need 
> 
> the counter internally.
> 
> 
> 
> There are probably other good reasons for using CHAR, but I can't think 
> 
> of any off hand. Maybe Bert knows - he knows lots of good stuff!
> 
> 
> 
> -- 
> 
> Cheers,
> 
> Norm. [TeamT]
>




------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/