Home » RDBMS Server » Performance Tuning » How do you cleanup chained rows!
How do you cleanup chained rows! [message #123970] Wed, 15 June 2005 15:07 Go to next message
jake374
Messages: 4
Registered: June 2005
Junior Member
I had a table with chained rows so I was going to rebuild it to clear them. I copied the rows off to another table and then truncated the original table. I then did an analyze on the table and it came back with 0 rows but now had a huge percentage of chained rows! How did this happen and how do I clean them up?
thanks
jake
Re: How do you cleanup chained rows! [message #123974 is a reply to message #123970] Wed, 15 June 2005 15:23 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
1. Identify the chained rows in a table
analyze table table_name list chained rows;

2. select the output
select head_rowid from chained_rows where table_name = 'table_name';

3. insert the chained record in a temp table, delete it from the base table and again insert it back to the base table.

insert into temp_table select * from table_name where rowid = head_rowid;

delete from table_name where rowid = head_rowid;

insert into table_name select * from temp_table;

Re: How do you cleanup chained rows! [message #123976 is a reply to message #123974] Wed, 15 June 2005 15:26 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
my earlier reply was the 1st step...but it seems u have already done it.
Check the db_block_size, it might be happening that row_size is bigger than block_size. Thats why record is unable to fit in the block

Re: How do you cleanup chained rows! [message #123977 is a reply to message #123970] Wed, 15 June 2005 15:26 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And if your chained rows are due to rows being bigger than your block size then you are out of luck.
Re: How do you cleanup chained rows! [message #123978 is a reply to message #123977] Wed, 15 June 2005 15:29 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
if it is the case and u r using 9i, u can have a diff block size at the tablespace level and move that table in a new tablespace.
Re: How do you cleanup chained rows! [message #123981 is a reply to message #123978] Wed, 15 June 2005 15:34 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
macdba wrote on Wed, 15 June 2005 16:29

if it is the case and u r using 9i, u can have a diff block size at the tablespace level and move that table in a new tablespace.


Excellent point.
Re: How do you cleanup chained rows! [message #123984 is a reply to message #123981] Wed, 15 June 2005 15:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Good points already given by macdba and smartin.
BTW what is the version of Oracle? What kind of table/data is this?
If you are using long/long raws , there is nothing much we can do except to live with that ( or move to lobs).
As macdba pointed, it is much efficient to fix only the chained rows (instead of rebuilding the whole table,incase the table is big)
Re: How do you cleanup chained rows! [message #123987 is a reply to message #123970] Wed, 15 June 2005 15:58 Go to previous messageGo to next message
jake374
Messages: 4
Registered: June 2005
Junior Member
thank you for all of your replys,
I am running 9.2.0.1.0
my tablespace is set to a blocksize of 8192.
my average row length is 38
the table description is:
SERIAL NOT NULL NUMBER(16)
STARTDATE NOT NULL DATE
ENDDATE DATE
SEVERITY NUMBER(16)
STATE NUMBER(16)
SERVERNAME NOT NULL VARCHAR2(64)
SERVERSERIAL NOT NULL NUMBER(16)

where I'm confused is that this table now does not have any data in it yet it shows. 1,700,176 continued row count
this was from the statistics tab in enterprise manager
regards,
ed

Re: How do you cleanup chained rows! [message #123989 is a reply to message #123987] Wed, 15 June 2005 16:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
That must the outdated statistics.
gather the stats again.
Re: How do you cleanup chained rows! [message #123990 is a reply to message #123970] Wed, 15 June 2005 16:03 Go to previous message
jake374
Messages: 4
Registered: June 2005
Junior Member
thanks,
I had done that and still got the same results but this time I deleted the stats first and then reran it. Now it is clear with no chained rows.
thanks again everyone.
Previous Topic: getting bad Performance during use
Next Topic: Locally Managed Table syntax
Goto Forum:
  


Current Time: Sat Sep 19 01:30:46 CDT 2020