分类：
2008-10-17 13:26:53
【中国IT实验室－技术报道】两者之间的区别：
行连接是指一个行在多个块中的情况，因为一个该行的长度超过了一个块的可用空间大小。
行迁移是指一个数据行不适合放入当前块而被重新定位到另一个块，但在原始块中保留一个指针，原始块中的指针是必需的，因为索引的rowid项仍然指向原始位置.
行连接通常与行的长度和oracle数据库块中的大小有关，而行迁移通常是当一个更新操作的长度增加且又要保持该行在同一块中，而该块又缺少可用空间时产生的问题，oracle在决定行连接之前先试图进行行迁移。
检查是否存在行迁移或是连接：
（1）ANALYZE TABLE order_hist LIST CHAINED ROWS;
（2）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
贴个document 9i里面的资料，说得比较详细:
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.
For example, this information can show whether PCTFREE is set appropriately for the table or cluster.
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 scrīpt. These scrīpts are provided by . They create a table named CHAINED_ROWS in the schema of the user
submitting the scrīpt.
--------------------------------------------------------------------------------
Note:
Your choice of scrīpt to execute for creating the CHAINED_ROWS table is dependent upon the compatibility level of your
database and the type of table you are analyzing. See the 9i 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:
Oracle9i Database Reference for a descrīption of the CHAINED_ROWS table
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;
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.
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 long CHAR or VARCHAR2 columns.
[1]
