Bank migration/Row link Learning in Oracle Database (iii) Row migration/Row Link Cleanup method

Because of the row link can only increase db_block_size to clear, and db_block_size after the creation of the database can not be changed, so there is no more than the purge of the row link, mainly for the row migration to talk about in the actual production system how to clear.

The removal of row migrations is generally divided into two steps: The first step is to control the growth of the row migration so that it is not increasing; the second step is to purge the previously existing row migrations.

As we all know, the main reason for row migrations is because the Pctfree parameters are set too small on the table. To achieve the first step to control the growth of the migration, you must set up a correct pctfree parameter, otherwise many new row migrations will occur even if the current row migration is cleared. Of course, this parameter is not the larger the better, if the pctfree set too large, will lead to low utilization of data blocks, resulting in a large amount of waste space, so you must set a reasonable pctfree parameters. How to determine a reasonable pctfree parameter on a table, there are generally two methods.

The first is a quantitative method of setting the size of a pctfree using a formula. Use the Analyze Table table_name estimate statistics command to parse the table that you want to modify Pctfree, and then view user_tables column values in Avg_row_len to get an average President Avg_row_ LEN1, then after a large number of table operations, again use the above command analysis table, get the second average president Avg_row_len2, and then use the formula (AVG_ROW_LEN2-AVG_ROW_LEN1)/(Avg_row_len2-avg_row _len1 + original Avg_row_len) The result is a quantitative calculation of a suitable pctfree value. This method, which is calculated quantitatively, may not necessarily be accurate, and is not very useful for systems that use RBO execution plans because they are analyzed. For example: avg_row_len_1 = 60,avg_row_len_2 = 70, the average modification is 10,pctfree should be adjusted to 100 * 10/(10 + 60) = 16.7%.

The second is the difference fine-tuning method, first query to the current table of the Pctfree value, and then monitor and adjust the pctfree parameters, each time to increase the size of pctfree, each increase in the proportion not more than 5%, and then use analyze table table_name The list CHAINED rows into Chained_rows command analyzes the growth of all row migrations and row links each time, with different growth ratios for different tables, a more rapid table Pctfree value for row migration growth, and less growth for slower-growing tables. Until the row migration of the table remains largely unchanged. But be careful not to pctfree too large, generally under 40% can be, otherwise it will cause a lot of space waste and increase database access IO.

By using the above method to control the growth of row migrations for the current table, you can begin to purge the rows that exist on the table before it is migrated. Whether or not to clear off row migration is related to the performance of the system can be greatly improved. Therefore, the migration of previous rows is certain and must be eliminated. There are many ways to clear out existing row migrations, but not all of them can apply to all situations, such as how many records are in a table, how many relationships are on the table, how many tables are migrated, and so on. Depending on the characteristics of the table and the specific situation, we should use different methods to clear the row migration. I'll cover the various ways to clear row migrations and the different situations they apply to each of them.

Method One: Traditional method of clearing row migration

The specific steps are as follows:

1. Executes the Utlchain.sql script in the $oracle_home/rdbms/admin directory to create the Chained_rows table.

@ $ORACLE _home/rdbms/admin/utlchain.sql

2. Place the rowid of rows migrated in a table (replaced by table_name) that has row migrations into the Chained_rows table.

ANALYZE TABLE table_name LIST CHAINED ROWS into chained_rows;

3. Save the row IDs for rows migrated in the table into a temporary table.

CREATE TABLE Table_name_temp as

SELECT * FROM table_name

WHERE rowID in

(SELECT Head_rowid from Chained_rows

WHERE table_name = ' table_name ');

4. Deletes rows of rows migrated from the original table.

DELETE table_name

WHERE rowID in

(SELECT Head_rowid

From Chained_rows

WHERE table_name = ' table_name ');

5. Remove and reinsert the deleted data from the temporary table into the original table, and delete the temporary table.

INSERT into table_name SELECT * from Table_name_temp;

DROP TABLE table_name_temp;

For this traditional method of clearing RM, the advantage is that the process is simple and easy to implement. But the disadvantage of this algorithm is that the table association is not considered many tables in most databases are related to tables that have a table associated with other tables, and have foreign key constraints, which makes it impossible to delete a row of rows at all in step 3, so the scope of the table that this method can apply to is limited. Can only be applied to tables on tables that do not have any foreign key associations. Since this method does not disable the index when inserting and deleting data, the main consumption time is to maintain the balance of the index tree at the time of deletion and insertion, which is relatively short when the number of records is small. But it's not acceptable to have the time spent on a table with a lot of records. Obviously, this approach is obviously undesirable when dealing with tables with large amounts of data.

The following is an example of clear row migration on a production database that has previously adjusted the table's Pctfree parameters to a suitable value:

sql>@ $ORACLE _home/rdbms/admin/utlchain.sql

Table created.

sql> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS into chained_rows;

Table analyzed.

Sql>select Count (*) from chained_rows;

TABLE_NAME COUNT (*)

------------------------------ ----------

CUSTOMER 21306

1 rows selected.

To view the restrictions that exist on the Customer table:

Sql>select constraint_name,constraint_type,table_name from user_constraints where table_name= ' CUSTOMER ';

CONSTRAINT_NAME C table_name

------------------------------ - ------------------------------

Pk_customer1 P CUSTOMER

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45556.htm