Re: Row Chaining

Parris Geiser wrote:
>
> Well, you could try delete/insert instead of update.
> But, of course that has its own set of problems ...
> So, you could try to only do the delete when the record got to a certain size
> or updated a particular field that, let's say, was generally the last one
> updated.
> parris
>
> Kristian Cibulskis (kristian_at_javanet.com) wrote:
>
> > I'm currently working on an order entry system using Oracle 7.3 as the back
> > end. We have a pretty serious problem involving row chaining. We have a
> > table called order_line_item. The average size of a row in this table when
> > it is inserted is ~80bytes. However, when the line item reaches it's final
> > state it has grown to ~160 bytes on average. It then stays in the system
> > at that size.
 

> > If I set my PCTFREE high enough to prevent chaining, it seems that I will
> > waste a lot of space due to these records that are all ready in their final
> > state. I tried preallocating space by setting one of the VARCHAR2s in the
> > table to be 100 spaces at insert time and then NULL it out at the first
> > update, but it did not seem to fix the problem. Any suggestion would help
> > greatly. Thanks in advance...
 

> > -kristian

I have not found chaining to be a major problem unless the percentage of chained rows rises above c.10% of the total.

Since pctfree is only relevant for inserts, you should be able to reduce the problem by setting it to, say, 50-55% (since rows grow from 80 to 160 bytes after insert "on average"). This will "waste space" only for those pages in which a majority of rows grow by less than the average. You will still get some chaining, of course, but the amount will depend on how much variation there is in growth *above* the average.

There is a problem, of course, if you want to export/import a table of mixed "infant" and "mature" rows. This you may have to live with unless you want to implement a solution on one of the following lines:

1. If row growth occurs in a small number of fixed stages, you could define separate tables for the "base" set of columns and for each incremental set. Updates are then replaced ny a series of inserts into different tables, each of more or less fixed row length. The original table can be re-created through a join of its components. This is not very useful unless the majority of updates occur to a limited set of known columns, or you are able to update the join view.
2. If a definable subset of rows has a much greater volatility than the rest, this subset might be made into a separate table with a different pctfree setting from the main table. The original relation is then re-created by a union of the two tables.

Hope this helps.

Chrysalis.

Chrysalis. Received on Sat Apr 26 1997 - 00:00:00 CDT