lots of chaining

I just realized one of my tables has thousands of chained rows. Does anyone know the best method of getting rid of this chaining. I would like to rebuild the table with larger extent sizes. I have heard the with oracle 8 there is an option to move a table and change the extent sizes can anyone tell me how this works.

thanks,
lacey

Your row is too big to fit in a database block so you have to increase the size your database block. How big is your block size, 2k? Now on the other hand, in case of row migration, you can increase PCTFREE so there will be enough room future row update.

ALTER TABLE {table_name} MOVE {tablespace_name};


http://www.dbasupport.com/forums/sho...8&pagenumber=1

Hi,

Rowchaining, Take a Export of the tables,

Drop the tables

Then import the table.

Regards

goodhealth I think increasing pctfree will worse the situation, since this is chaining not row migration.
exp/imp wouldnt wok unless you change the pctfree & pctused before import

For row chaining ,u have only one solution ,i.e. You change the DB_BLOCK_SIZE init.ora parameter.Pl note that u have to recreate the database.I hope that u must be aware about the reparkations of recreating the database.Now, onwards before
fixing the DB_BLCK_SIZE ,think about the average row size of your tables.

SANJAY
OCP DBA

Quote:

---

Originally posted by pando
goodhealth I think increasing pctfree will worse the situation, since this is chaining not row migration.
exp/imp wouldnt wok unless you change the pctfree & pctused before import

---

Pando, I don't think your remark about increasing PCTFREE will make situation about rowchaining is correct.

If this is real *rowchaining* and not *rowmigration* or *combination of both* then PCTFREE has no efect on it whatsoever. Rowchaining is a situation when row is to big to fit in an empty block. Oracle allways try to place a whole row in a single block, regardles of the setting of PCTFREE. For example, if you have 4K blocksize and you insert the row that occupies 3K in a table that has PCTFREE set to 50%, oracle will put the row in a single block and will not split it in two different blocks to preserve free space as specified by PCTFREE. On the other hand, if the problem is rowmigration then increasing PCTFREE has a huge impact and can greatly reduce rowmigration.

You can easily find out which type of "continued" rows you actualy have by exp/imp the table or by rebuilding it with MOVE. If after rebuilding the table no more "conitinued" rows are reported then "continued" rows were originaly migrated ones and increasing the PCTFREE will help prevent migrations in the future. If there are still many "continued" rows then they are chained ones and only recreating the database with bigger block size may help.

In any case, changing the storage parameters for the extents have no efect on rowchaining or rowmigration, as was guesed in lacey's original post.

absolutely corrct!
if the rows are so large then recreating would be only solution.
Seems like 9i would allow multiple block sizes in the database, at the end

Hi Pando,

On row migration, I think we were talking about the same thing. Maybe my posting wasn't clear on PCTFREE.

Alternative method and then a question:
1. run utlchain.sql
2. analyze table your_table_name list chained rows;
3. select * from chained_rows where table_name = your_table_name;
4. create table asdf as select * from your_table_name where rowid in
(select head_rowid from chained_rows where table_name ='your_table_name';
5.delete from your_table_name where rowid in
(select head_rowid from chained_rows where table_name ='your_table_name');
6. insert into your_table_name select * from asdf;
7. drop table asdf;
8. clean up chained_rows table
9. analyze table your_table_name list chained rows;

Anything rows in the last query are chained. The above procedure fixes migrated rows only.

Now my question: Does this produce the same results as the move table option?

Isn't it true that alter table move only works with index-organized tables?

MOVE TABLE works with all tables EXCEPT IOT's.

JDoyle,

If your table is not very large and you are on 8i, I think the 'alter table move' option is better.

Your method is good and it does work. Prior to 8i, there was
no 'alter move' command, so the only options were your suggestion and exp/imp.

The only problem with your suggestion is that it can put a lot of blocks on freelists, which basically means that now your table is somewhat fragmented (which is not as bad as chained rows, I don't think).

But if your table is very big, you might not have the time to do an 'alter move', then rebuild all of the indexes, then re-analyze your tables, so that's when your suggestion can be handy.

-John

Oh yeah -

2 more steps you might want to include in JDoyle's method :

Disable any FK constraints and triggers on your table
(if there are any) and then enable them again afterwards!

:-)

-John