row chaing 8.1.7.0.0

[netbar]

Hi what's the best way to eliminate row chaining in Oracle 8i I have a table with over 40,000 chained rows it is used for reporting and I am sure detremental to performance, what the best way to eliminate it!

Can I use alter table move..

[adewri]

Use bigger db_block_size or use bigger pctfree.

RTFM

[SANJAY_G]

Originally posted by netbar
Can I use alter table move..

Yes, ALTER TABLE MOVE can fix them but to avoid re-ocurrance check your PCTFREE.

[netbar]

Thanks,

Apologies for new thread early on a monday morning...

[adewri]

Please do not use two seperate threads for same question.

As you asked :

What about the existing problem??

---
This will be solved with alter table move command as the table would be rebuild. And if you set the pctfree to a reasonable size then your existing chaining will also get solved.

HTH

[ggnanaraj]

You can use this code to remove chained rows in your table. Proof read the script and check it on test database before you use it on production.

HTH.

ACCEPT chaintabl PROMPT 'Enter the table with chained rows: '  
  
drop table chaintemp;  
drop table chained_rows; 
  
start d:\oracle\ora81\rdbms\admin\utlchain  

analyze table &chaintabl   
list chained rows into chained_rows;   
  
 
create table chaintemp as  
select *  
from &chaintabl  
where rowid in (select head_rowid  
		from chained_rows);  
  
  
delete from &chaintabl  
where rowid in (select head_rowid  
		from chained_rows); 
  
insert into &chaintabl  
select *  
from chaintemp; 
  
drop table chaintemp;

[adewri]

ggnanaraj,

this solution would be a temporary one, what about the future inserts and updates ??

Go with alter table move.

[jovery]

Of course you may find that depending on the data in the rows (eg. if your usings LONG's) that row chaining is unavoidable and you will have to do as Amar said earlier and recreate the database with a larger block size.

Regards