Re: VARCHAR2 storage on a page

The replies you've received are correct (except they use the term 'chained' when they more accurately mean that the row that expands in size will be *migrated* into another block).

However, I still think there is merit in avoiding unnecessary use of varchar2 datatypes, because row migration is not funny when you get it, and causes immediate drop-offs in performance. I'd rather waste a bit of disk space and limit the amount of row migration I suffer from (or potentially suffer from), thanks very much!

That said, if you don't use NOT NULLs a lot, that makes a row inherently variable in size, too. And number is an inherently variable data type also. Which means you can only eliminate the risk of row migration if everything is CHAR, all columns are NOT NULL and you don't store NUMBERS... all of which is a tad unlikely. Therefore, the risk of row migration is something you have to live with, and therefore CHAR v. VARCHAR2 is a matter of personal preference.

I still avoid VARCHAR2s, however, where I don't think it meaningful (such as your short 6-character fields).

Regards
HJR "Jacob Salomon" <JSalomon_at_bn.com> 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.)
>
> 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 Wed May 08 2002 - 05:09:44 CDT