Re: VARCHAR2 storage on a page

Thanks, Jim.

It is a relief to know I won't be flooding the database with nearly empty pages.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<uEGB8.241022$CH1.179926_at_sccrnsc02>...
> 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 Tue May 07 2002 - 11:32:20 CDT