Oracle doesn't work that way - not to flame on Informix, Oracle just is
different. In Oracle if the amount of data is 200 bytes (regardless of the
varchar2 max size) and it will fit and the block - or page - is on the free
list then it will insert it into that block.
So if you later update that row so it does not fit in that block then it
will chain it to another row. So what you want to do is read about pctfree
and pctused in the Oracle docs.
Jim
"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 Mon May 06 2002 - 21:06:18 CDT
