Re: VARCHAR - Physical storage question

"Ben Heuer" <queerczarNOquSPAM_at_unforgettable.com.invalid> wrote in message news:03f9fd17.f5890caa_at_usw-ex0104-031.remarq.com...
> Hello
>
> I'd like to know how the VARCHAR handles the data? I understand
> that it allocates only the amount of space that the column
> actually contains? But what if the column content is modified?
>
> For instance, lets say I have
>
> USER_NAME VARCHAR2(20)
>
> Now, I do an insert:
>
> INSERT INTO TABLE VALUES ('BEN HEUER')
>
> This is just 9 characters. I undrstand that physically only 9
> chars will be allocated?
>

Yup.

> Now, lets say I update this field to BEN_HEUER_1234. How does
> the varchar physically allocate space for it, because there must
> be records after the original record as well...is a separate
> space created for it and these two are related based on
> something?
>

What happens is that the "BEN_HEUER" record is effectively deleted, and a new entry for "BEN_HEUER_1234" is created. If everything is set up just right, the deletion and the creation all happen within the same Oracle block, and your record effectively just sloshes around inside that block. By the time DBWR flushes it down to disk, everything is fine, and your newly-resized record is located where the old one would have been.

Assuming, that is, that there is enough room for the data to slosh around the block. If, on the other hand, you have filled your block up too full, such that there is no room to accomodate the 'new' record, then it will be created in another block entirely -and the original record will still be deleted, but a stump will be left that points to the new one. So now what happens is that an index search for your record will first be pointed to the location of the original record, which in turn will point it off to the location of the new, larger record.

That's called row migration (ie, the record has sloshed outside of the original block into a completely separate block), and it's a performance nightmare, and requires a fair degree of effort to fix up.

So, instead of fixing it up after the event, you prevent it from happening in the first place, by ensuring that your blocks do not get filled up to the brim -there is always some 'slosh room' in them, to allow for records to grow. You do this by setting PCTFREE for each segment that you create. It means you are wasting space in each block (which is not good) to avoid row migration (which is worse) -think of it like the gap they leave between train rails, to allow for expansion in hot weather. Or the gap you leave at the top of a wine glass which allows you to pick up the glass without spilling its contents.

By default, PCTFREE is set to 10% -which may be too low if your tables contain a lot of VARCHAR2 and NUMBER fields (because numbers are stored in variable length format too), and get updated frequently. It also is probably too low if *nothing* is declared 'NOT NULL' (because that means you can enter a record with stacks of fields missing, and then subsequently go back and fill them in -the filling in means the record has grown in length). If you have a table in which everything is NOT NULL'ed, only CHAR is used, and there are no NUMBER fields, then you can get away with a PCTFREE of 0 -but that is so unlikely to happen that I wouldn't worry about it!

You can detect whether row migration has happened by doing a 'analyze table compute (or estimate) statistics, and then querying the CHAIN_CNT field of the DBA_TABLES view.

If there's a number there, you've got row migration (it could be row chaining, too, but that's another story). Fixing it up is a pain -either export, drop and import, or 'create table new_emp as select * from old_emp' then drop old_emp and rename new_emp to be old_emp.

Hope that helps a bit,
HJR
> Thanks!
>
>
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
Received on Sun Jul 30 2000 - 00:00:00 CDT