排除表中的行連結和行遷移
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.
1. Use the ANALYZE statement to collect information about migrated and chained rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;
2. 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.
3. 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:
4. 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');
5. 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');
6. Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist SELECT * FROM int_order_hist;
7. Drop the intermediate table:
DROP TABLE int_order_history;
8. Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';
9. 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 large CHAR or VARCHAR2 columns
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.
Note:Your choice of script to execute for creating the CHAINED_ ROWS table depends on 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;
1. Use the ANALYZE statement to collect information about migrated and chained rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;
2. 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.
3. 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:
4. 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');
5. 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');
6. Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist SELECT * FROM int_order_hist;
7. Drop the intermediate table:
DROP TABLE int_order_history;
8. Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';
9. 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 large CHAR or VARCHAR2 columns
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.
Note:Your choice of script to execute for creating the CHAINED_ ROWS table depends on 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;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29802484/viewspace-2121478/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 模擬Oracle行遷移和行連結Oracle
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- [20190120]行連結遷移與dml.txt
- [20180402]行連結行遷移與ITL槽6.txt
- 使用Mobilenet和Keras進行遷移學習!Keras遷移學習
- 遷移執行緒migration執行緒
- 1.5 使用nvicat和kettle進行全量遷移
- 聊聊國產資料庫遷移中的表連線效能問題資料庫
- Laravel5.5執行表遷移命令出現表為空的解決方案Laravel
- 資料遷移(1)——通過資料泵表結構批量遷移
- CMake 進行多專案中dll的編譯和連結編譯
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- QEMU-KVM中的多執行緒壓縮遷移技術執行緒
- [20180327]行遷移與ITL浪費.txt
- 從 Oracle 到 TiDB,全鏈路資料遷移平臺核心能力和杭州銀行遷移實踐OracleTiDB
- [20230425]CBO cost與行遷移關係.txt
- 杉巖資料銀行Documentum遷移方案
- 使用Conda Pack進行環境打包遷移
- 對Oracle分割槽表進行表空間遷移並處理ORA-14511問題Oracle
- “遷移策略+新容器執行時”應對有狀態應用的冷熱遷移挑戰
- gitlab的遷移和升級Gitlab
- Oracle中表空間、表、索引的遷移Oracle索引
- EF Core 小技巧:遷移已經應用到資料庫,如何進行遷移回退操作?資料庫
- mysql 大表mysqldump遷移方案MySql
- 連載一:Oracle遷移文件大全Oracle
- 連載二:Oracle遷移文章大全Oracle
- 資料表結構更新後,遷移檔案怎麼使用?
- 表結構遷移檔案Identifier name 'xxxxxx' is too long解決IDE
- Kubernetes怎麼進行NFS動態儲存遷移NFS
- Terraform初探:遷移本地專案到Terraform Cloud執行ORMCloud
- 使用dbeaver 用csv 檔案進行資料遷移
- MySQL 遷移表空間,備份單表MySql
- 使用Liquibase和Spring Boot進行資料庫遷移的一站式指南 - reflectoringUISpring Boot資料庫
- [20180730]exadata與行連結.txt
- Laravel 使用 sql 語句 和 sql 檔案 來建立執行資料庫遷移LaravelSQL資料庫
- table/index/LOBINDEX遷移表空間Index
- 達夢(DM)資料庫的表空間建立和遷移維護資料庫
- 簡談linux中軟連結和硬連結的區別Linux