What is the difference between chained and migrated rows? Also what query should be used to display these rows? (I'm using Oracle 11gR2 database)
A migrated row would be a row who's column data was completely transferred from one block to another due to an update. The original block would essentially only have a "forwarding address" stored for that row.
A chained row would have parts of its column data in multiple blocks. The original block would contain both actual column data and a forwarding address for the rest of it. (You can get rows chained to more than two blocks.)
Both are implemented the same way deep down, so they're really two aspects of the same thing.
Also note that for tables with more than 255 columns, all rows are technically chained - one "row piece" can only contain 255 column values. The chaining can happen in the same block, or with other blocks depending on space availability (and isn't particularly "bad" if all the data ends up in the same block).
The only way, as far as I know, to get accurate data on row chaining is to use:
This is potentially expensive, the whole table needs to be scanned. Statistics gathering doesn't fill the
You can monitor the
The Secrets of Oracle Row Chaining and Migration has interesting information about chained and migrated rows, how you measure them, and potential ways of fixing them.
1 From Statistics Descriptions:
See also Table Fetch by Continued Row.
A more "proactive" way to find if you have issues with chained rows is to use OEM and the segment Advisor. (Administration –> Storage –> Segment Advisor -> Chained Row Analysis) Segment Advisor runs on a schedule and looks for curiously long operations that might signal a chained row issue. It then does a deeper analysis during your database's maintenance window, and gives you the option to correct the problem...but I prefer to correct the problem outside of OEM.
To resolve the problem, you have a few options. Reading the answer above, its strange that a recent Oracle doc mentions doing an export/import...that works, but its probably not the fastest or easiest option.
A much faster way than an export/import is to do an "alter table table_name move;"...which will reorg the table in the same tablespace. This can also be done parallelized. After that, you'll need to rebuild all the indexes on that table.
Another (online) option is to use dbms_redefinition. This is great when it works, but its more complicated than the other options.
You could also do an insert into a new table, (insert into new_table_name select * from table_name), rename the tables so the new table has the old name and then rebuild the indexes.
In my experience the best way is usually the alter table move, unless you have storage capacity constraints.