消除表中行迁移和链接
Listing Chained Rows of Tables and Clusters
You can look at the chained and migrated rows of a table or cluster using the ANALYZE
statement with the LIST
CHAINED
ROWS
clause. The results of this statement are stored in a specified table created explicitly to accept the information returned by the LIST
CHAINED
ROWS
clause. These results are useful in determining whether you have enough room for updates to rows.
我们可以利用ANALYZE命令的LIST CHAINED ROWS子句查看表或者簇中链接和迁移的行。这条命令的结果被存储在一个指定的,明确创建用于接收LIST CHAINED ROWS子句返回信息的表中。这些返回的结果对于决定我们是否有足够的空间去更新行数据非常有用。
Creating a CHAINED_ROWS Table
To create the table to accept data returned by an ANALYZE...LIST
CHAINED
ROWS
statement, execute the UTLCHAIN.SQL
or UTLCHN1.SQL
script. These scripts are provided by the database. They create a table named CHAINED_ROWS
in the schema of the user submitting the script.
在我的10.2.0.4 RAC DB环境中脚本为:
$cd $ORACLE_HOME/rdbms/admin
$ cat utlchain.sql
rem
rem $Header: utlchain.sql 07-may-96.19:40:01 sbasu Exp $
rem
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLCHAIN.SQL
Rem FUNCTION
Rem Creates the default table for storing the output of the
Rem analyze list chained rows command
Rem NOTES
Rem MODIFIED
Rem syeung 06/17/98 - add subpartition_name
Rem mmonajje 05/21/96 - Replace timestamp col name with analyze_timestam
Rem sbasu 05/07/96 - Remove echo setting
Rem ssamu 08/14/95 - merge PTI with Objects
Rem ssamu 07/24/95 - add field for partition name
Rem glumpkin 10/19/92 - Renamed from CHAINROW.SQL
Rem ggatlin 03/09/92 - add set echo on
Rem rlim 04/29/91 - change char to varchar2
Rem Klein 01/10/91 - add owner name for chained rows
Rem Klein 12/04/90 - Creation
Rem
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
$ cat utlchn1.sql
Rem
Rem $Header: utlchn1.sql 24-jun-99.07:57:57 echong Exp $
Rem
Rem utlchn1.sql
Rem
Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem NAME
Rem utlchn1.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem echong 06/24/99 - rename
Rem syeung 06/22/98 - add subpartition_name
Rem echong 06/05/98 - chained rows table with urowid type
Rem echong 06/05/98 - Created
Rem
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid urowid,
analyze_timestamp date
);
Note:
Your choice of script to execute for creating theCHAINED_ROWS
table is
dependent upon the compatibility level of your database and the type of table you are analyzing. See the
Oracle Database SQL Reference for more information.
After a CHAINED_ROWS
table is created, you specify it in the INTO
clause of the ANALYZE
statement. For example, the following statement inserts rows containing information about the chained rows in the emp_dept
cluster into the CHAINED_ROWS
table:
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
See Also:
Oracle Database Reference for a description of the
CHAINED_ROWS
table"Using the Segment Advisor" for information on how the Segment Advisor reports tables with excess row chaining.
Eliminating Migrated or Chained Rows in a Table
You can use the information in the CHAINED_ROWS
table to reduce or eliminate migrated and chained rows in an existing table. Use the following procedure.
以下是消除表中行迁移和链接的步骤
Use the
ANALYZE
statement to collect information about migrated and chained rows.ANALYZE TABLE order_hist LIST CHAINED ROWS; 使用ANALYZE收集迁移和链接的行的信息
Query the output table:
SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST'; OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP ---------- ---------- -----... ------------------ --------- SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96 SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96 SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96
The output lists all rows that are either migrated or chained. --查询出来的结果是发生迁移或者链接的行信息
If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows by continuing through the following steps:
Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
CREATE TABLE int_order_hist AS SELECT * FROM order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST'); 创建中间表用于临时存储发生迁移和链接的行
Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST'); 删除发生迁移和链接的行
Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist SELECT * FROM int_order_hist; 把刚才创建的中间临时表中的数据插入到原来的表中
Drop the intermediate table:
DROP TABLE int_order_history; 删除中间表
Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST'; 删除第一步中收集的信息
Use the
ANALYZE
statement again, and query the output table.
重新执行ANALYZE命令,并查询输出表
Any rows that appear in the output table are chained. You can eliminate chained rows only by increasing your data block size. It might not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a LONG
column or large CHAR
or VARCHAR2
columns.