Re: VARCHAR2 storage on a page

Jacob Salomon wrote:

> 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.)
>
> 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 --------------+

Jim is correct. But this might be a good time to point out that much of your experience with Informix will likely lead you to make assumptions about Oracle that are not valid. And those assumptions can lead to corrupt data, lack of scalability, and other less than innocuous problems.

Daniel Morgan Received on Tue May 07 2002 - 13:05:54 CDT