1. Row migration
When an update is issued to increase the number of records, the remaining space in the block is not sufficient to hold this record, it will result in a row migration, the ROWID will not change when the row is migrated, the original block will have a pointer to the address in the new block, the row migration will have a performance impact, Because reading this record will read two blocks.
- Row migration: Causes the application to need access to more chunks of data, performance degradation.
- Prevention of row migration: 1. Increase the pctfree of the data block by 2. Enlarge chunk size for table space
Ways to eliminate row migration:
- Create a row migration table, and create a chained_rows table $ORACLE the Utlchain.sql script in the _home/rdbms/admin directory.
- Disables all restrictions that are associated to this table on all other tables. such as foreign keys.
- rowID of rows that produce row migrations in tables with row migrations (replaced with table_name) are placed into the Chained_rows table.
- Row IDs for rows in a table are saved in a staging table.
- Deletes a row of rows that exist in the original table.
- Remove and reinsert the deleted data from the staging table into the original table, and delete the temporary table.
- Enable all restrictions on all other tables that are associated to this table.
Note: Step 3, complete with the following statement, analyze table table_name list chained rows into chained_rows;
The function is to rowid the record that produces the row migration into the table chained_rows.
2. Line link
A row connection occurs when a block is not sufficient to hold the next record, and this time Oracle divides the record into several blocks, which are stored in several blocks, and then chain up the block. Row connections can also affect performance because reading a record reads at least two blocks.
- Row links can only be resolved by using a larger chunk size.
- The method of eliminating row links is consistent with eliminating row migrations.
ORACLE row migration and row linking