Re: A little baffled by "chained rows"....

On Tue, 12 Aug 2003 17:32:42 GMT, Jack Wang <nospam_at_nospam.com> wrote:

>> insert into blah select * from X; (re-inserts the old rows, and because
>> it's an insert, they can't possibly migrate).
>
> Yes, this solves row migration. But if the length of inserted row exceeds
> block size, row chaining would still occur? Any suggestion on how to
> avoid
> chained row in such case? Is it necessary to re-design the table
> structure
> to use CLOB in replace of char/varchar2?
>
> Thanks.
> Jack

Well, if it's row chaining, it can only be because the row length is bigger than your block size (or, I should say, your blocksize minus pctfree). So, assuming you don't have a daft pctfree, about the only thing you can do to cure it is to change the db_block_size. Er, except that you can't, because that's fixed for ever when the database is created. So it's a case of drop the database and re-create it with a bigger block size, or re-create it.

Unless you're on Oracle 9, where you could conceivably create a new tablespace with a bigger block size, and then alter table blah move tablespace newone... and hopefully, the rows should now fit inside the bigger blocks.

But if earlier than 9? It's uncurable.

On a table-by-table basis, it's possible to do something as you suggest, but that doesn't truly fix the problem. CLOBS merely formalise the process of chaining: they still end up being stored 'out of line', with a link from the text/numbers bit of the row to the CLOBby bits. Sometimes, especially when CLOB/BLOB data is involved, chaining is just an inevitable fact of life; the nature of the beast. And you just live with it.

Regards
HJR Received on Tue Aug 12 2003 - 15:13:21 CDT