Re: VARCHAR2 storage on a page

JSalomon_at_bn.com (Jacob Salomon) wrote in message news:<38b6a68e.0205061500.5feba82_at_posting.google.com>...
> Greetings, Oracle DBA community.
>
> I am an Oracle newbie in the position of designing the layout and
> datatypes of a new Oracle database. Qualifications: Several years as an
> Informix DBA, with workable knowledge of the internal behavior of that
> engine.
>
> My colleague insists that every character column be defined as datatype
> VARCHAR2(whatever is appropriate). My Informix experience with VARCHAR
> warns me that overuse of VARCHAR can lead to some very inefficient use
> of disk space. Let's go to a specific example - and I'm not giving away
> any technical secrets that Informix doesn't publish anyway.
>
> Under Informix, suppose a row is of variable length [due to one of more
> VARCHAR columns] has a maximum size of 600 bytes. Suppose further that a
> page has 500 bytes available and the new row I want to insert requires
> only 200 bytes. Informix's placement algorithm says: "Insert the row
> into the page only if the row at its maximum size - 600 bytes - could
> fit." Well, in this case Informix would go look for another page, one
> with at least 600 bytes available. (No flames, please; it works well
> and seldom really wastes significant space.)

Oracle uses different approach. It stores real length of the variable, no matter what max size is. And if the size of variable increases, Oracle tries to allocate extra space within block. If no space available, block is chained. By the way, all numbers in Oracle are variable size too. It's all described in Oracle Concepts documentation. In short, your collegue is right, VARCHAR2 is more efficient than CHAR.

>
> Having taught classes on Informix administration, I have preached against
> overuse of VARCHARs that cause a row size to range wildly, like 200 to
> 1500 bytes. Now here is my colleague telling me to make EVERYTHING
> either a VARCHAR2 or a NUMBER, even relatively short fields with max
> length of 6 characters. I am concerned about the Oracle algorithms for
> placement of rows in a page - am I looking at the same kind of silliness
> that would be provoked in an Informix database.
>
> Thanks for any help here.
> +------------ Jacob Salomon JSalomon_at_bn.com -- -------------- --------+
> | Men occasionally stumble over the truth, but most of them pick |
> | themselves up and hurry off as if nothing had happened. |
> +------------------------------- Sir Winston Churchill --------------+
Received on Tue May 07 2002 - 11:18:25 CDT