Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jayaraman.

Asked: October 11, 2001 - 8:38 pm UTC

Last updated: June 07, 2004 - 4:40 pm UTC

Version: 8.1.6.0.0

Viewed 1000+ times

You Asked

Hi Tom,

Kindly correct me if i am wrong in my understanding and please explain.

Row chaining occurs when the row is too large to fit into any one block and hence it is split and stored in different blocks. This is done only after oracle tries to fit the row into any one block in the particular segment and failing which the row is split and stored. Each piece of the row will have a head and a tail like a linked list that has the address of the block of its preceding piece and succeeding piece.

Now row migration is different in the aspect that it is possible to fit the row into any one block in the segment that has enough space to accomodate the row. This will have only a pointer in the original block of the row that points to its new block address.

Both the above conditions will occur only when the requisite pctfree/pctused block storage parameters are satisfied. Correct?

Will the analyze command for listing chained rows include the migrated rows as well? Going through the earlier questions in asktom has not cleared my doubt.

Thanks in advance,

Regards,
Ashok

and Tom said...

A migrated row is just a special case of a chained row (a migrated row is a chained row, a chained row may or may not be a migrated row)

So, when you analyze the table to list chained rows, it includes migrated rows as well.

The explaination you give is correct in its content.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, October 14, 2001 - 7:49 pm UTC

Thanks

Why do migrated rows need a pointer.

Amar, March 03, 2003 - 1:04 am UTC

Hi,
What i wanted to ask was why does ORACLE need a pointer in the original block to point to the new block? Why not just directly go to the new block the way it goes to the older block??
Why doesn't Oracle simply update the table directory and row directory to point to the new block when migration occurs??
One reason i could think of was to preserve the rowid... any other reasons ??

Tom Kyte
March 03, 2003 - 6:49 am UTC

to preserve the rowid. to do otherwise would require us to update each and every index during a migration, and if you are using rowid based snapshots....

Thanks

Amar, March 03, 2003 - 7:48 am UTC

And also i would need to rebuild the indexes again if that happens :)

Oops!

Amar, March 03, 2003 - 7:51 am UTC

oops missed that, you had already said regarding the indexes

Any way,Thanks a lot.

Why migrate a row?

Arun Gupta, June 07, 2004 - 4:35 pm UTC

Tom,
While going through the concepts manual of 10g, I just thought that since in newer versions of Oracle, rowid can change (alter table..move tablespace, row movement enable etc.), why does Oracle still do row migration? If the updated row is too big to fit in the block, why not move it to a new block and assign a new rowid? Is it because of index maintenance?
Thanks

Tom Kyte
June 07, 2004 - 4:40 pm UTC

it can move -- but by default won't.

many applications would break if it moved for something as simple as an update.

Also, an update of a non-indexed column -- that caused a migration -- of a row with 10 indexes on it - would all of a sudden cause 10 indexes to be updated, which would be bad.




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.