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 script. These scripts are provided by Oracle. They create a table named CHAINED_ROWS in the schema of the user
--------------------------------------------------------------------------------
Note:
Your choice of script. to execute for creating the CHAINED_ROWS table is dependent upon the compatibility level of your
Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
The output lists all rows that are either migrated or chained.
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.
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 script. These scripts are provided by Oracle. They create a table named CHAINED_ROWS in the schema of the user
submitting the script.
--------------------------------------------------------------------------------
Note:
Your choice of script. 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.
--------------------------------------------------------------------------------
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;
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.
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 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
---------- ---------- -----... ------------------ ---------
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');
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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12361284/viewspace-594411/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- chained rows analyzeAI
- Validating Tables, Indexes, Clusters, and Materialized ViewsIndexZedView
- Script: To remove Chained Rows from a Table (Doc ID 1019556.6)REMAI
- 查詢行遷移及消除行遷移(chained rows)AI
- Oracle 19c Concepts(02):Tables and Table ClustersOracle
- ORA-30926: unable to get a stable set of rows in the source tables
- oracle11g_如何模擬產生行連結或行遷移chained_rowsOracleAI
- list chained rowAI
- Overview of Clusters (238)View
- Row Migration和row chainedAI
- on commit delete rows and on commit preserve rowsMITdelete
- 拉取易倉API的亞馬遜Listing資料-listing表現介面API亞馬遜
- Overview of Hash Clusters (239)View
- dba_tables,dba_all_tables,user_tables,all_tables有什麼區別
- FIRST_ROWS和FIRST_ROWS_n的區別
- Oracle 21C Flex ClustersOracleFlex
- Oracle 21C Extended ClustersOracle
- Oracle Real Application Clusters GFSOracleAPP
- 【原創】論Optimizer的工作模式ALL_ROWS&FIRST_ROWS模式
- FIRST_ROWS和FIRST_ROWS(N)的區別 (zt)
- SAP RETAIL 商品LISTING方法之一AI
- Recipe 5.2. Listing a Table's Columns
- JavaScript rows 屬性JavaScript
- postgresql delete duplicated rowsSQLdelete
- flex 3 rows layoutFlex
- Oracle TablesOracle
- 沃爾瑪拉取listing和庫存
- F. Color Rows and Columns
- RH436 UNIT 1 CLUSTERS AND STORAGE薦
- How a Database Is Mounted with Real Application Clusters (294)DatabaseAPP
- CRS and 10g Real Application ClustersAPP
- Oracle Externale TablesOracle
- Oracle - Tables/IndexesOracleIndex
- Oracle X$TablesOracle
- Oracle X$ TablesOracle
- Edit SAP tables
- Oracle Partitioned TablesOracle
- MySQL 5.5 LOCK TABLES 和 UNLOCK TABLES 語句介紹MySql