Oracle row migration and row Link

Oracle row migration and row link definition: Row migration: Because of update, the row length, the original block can no longer be placed, the row is moved into the new block, row link: the line is too big at the beginning. For example, if you have inserted a row of bytes, you must store the row in two parts. Therefore, the time sequence of the row length can be used as a judgment basis: row-based migration of www.2cto.com Oracle will migrate the entire row of data to a new data block, and place the original space of the row with only one pointer pointing to the new location of the row, in addition, the remaining space of the row is no longer used by the database. We call the remaining space empty. This is the main reason for table fragmentation. Table fragmentation is also inevitable, but we can reduce it to an acceptable level. Note: even if a row migration occurs, the rowid of the row to be migrated remains unchanged. This is also the reason why the row migration will cause database I/O performance degradation. Oracle uses one or more data blocks linked to the row to accommodate the data of the row. Row connections often occur when large rows are inserted, such as long, long row, and lob data. In these cases, downstream links are inevitable. Www.2cto.com affects the performance of the database. read two data blocks in one row, that is to say, the performance of logical read insert or update operations is poor. The performance of select statements that query linked or migrated rows using indexes is poor, because they need additional I/O to detect row migration and row link [SQL] <span xmlns =" http://www.w3.org/1999/xhtml "Style =" "> SQL> conn/as sysdba Connected. SQL> @? /Rdbms/admin/utlchain. SQL Table created. SQL> analyze table hr. parameters list chained rows; Table analyzed. SQL> select * from chained_rows; no rows selected </span> In addition, how can we know tables with severe row migration (row link? The CHAINED_CNT column in The DBA_TABLES view, which contains the link row count of the table. Although row migration and row link are two different things, they are considered the same thing in oracle. Therefore, when detecting row migration and row link, you should carefully analyze whether row migration or row link is currently being processed. Solution o in most cases, row links cannot be overcome, especially when a table contains columns such as LONGS and LOBs. When there are a large number of linked rows in different tables and the row length of which tables is not very long, you can use a larger block size to reconstruct the database. For example, the data block size of your database is 4 K, but the average length of your row is 6 k, then you can use 8 K data blocks to reconstruct the database to solve the row link problem. The migration of www.2cto.com o is mainly caused by the small value of the PCTFREE parameter, which leads to the absence of sufficient free space for the update operation. To avoid row migration, appropriate PCTFREE values should be set for all modified tables to reserve sufficient space for data modification within each data block. We can avoid row migration by adding the PCTFREE value, but this solution is at the cost of sacrificing more space, which is what we usually call space-for-efficiency. In addition, the increase of the PCTFREE value can only alleviate the phenomenon of row migration, but cannot completely solve the row migration problem. Therefore, a better solution is to set the appropriate PCTFREE value, if row migration is serious, reorganize the table data. The following is the procedure for reorganizing row migration data (this method has also become CTAS): [SQL] <span xmlns =" http://www.w3.org/1999/xhtml "Style =" "> -- Get the name of the table with migrated rows: ACCEPT table_name PROMPT 'Enter the name of the table with migrated rows: '-- Clean up from last execution set echo off drop table migrated_rows; drop table chained_rows; -- Create the CHAINED_ROWS table @... /rdbms/admin/utlchain. SQL set echo on spool fix_mig -- List the chained and migrated rows ANALYZE TABLE & table_name LIST CHAINED ROWS; -- Copy the chained/migrated rows to another table create table migrated_rows as SELECT orig. * FROM & table_name orig, chained_rows cr WHERE orig. rowid = cr. head_rowid AND cr. table_name = upper ('& table_name'); www.2cto.com -- Delete the chained/migrated rows from the original table delete from & table_name WHERE rowid IN (SELECT head_rowid FROM chained_rows ); -- Copy the chained/migrated rows back into the original table insert into & table_name SELECT * FROM migrated_rows; spool off </span>