什么是行迁移行链接?#以下描述来自Oracle11gR2Document1.行链接Therowistoolargetofitintoonedatablockwhenitisfirstinserted.Inrowchaining,OracleDatabasestor
什么是行迁移 & 行链接?
# 以下描述来自Oracle 11gR2 Document
1. 行链接
The row is too large to fit into one data block when it is first inserted.
In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. Row chaining most often occurs with large rows. Examples include rows that contain a column of data type LONG or LONG RAW, a VARCHAR2(4000) column in a 2 KB block, or a row with a huge number of columns. Row chaining in these cases is unavoidable.
2. 行迁移
A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row.
In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or “forwarding address” to the new block containing the migrated row. The rowid of a migrated row does not change.
行者AI
行者AI绘图创作，唤醒新的灵感，创造更多可能
100 查看详情
本文的主题是消除行迁移,既然如此,那就必须先模拟出行迁移来:
–创建chained_rows表SQL> @$ORACLE_HOME/rdbms/admin/utlchain.sqlTable created.SQL>–创建测试表EMPLOYEESSQL> CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR.EMPLOYEES;Table created.SQL> COMMIT;Commit complete.SQL>–分析测试表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>–查询可知当前测试表EMPLOYEES上不存在行迁移SQL> select count(*) from chained_rows where table_name=’EMPLOYEES’; COUNT(*)———-0SQL>–更新测试表EMPLOYEES结构SQL> alter table employees modify FIRST_NAME varchar2(2000);Table altered.SQL> alter table employees modify LAST_NAME varchar2(2000);Table altered.SQL> alter table employees modify EMAIL varchar2(2000);Table altered.SQL> alter table employees modify PHONE_NUMBER varchar2(2000);Table altered.SQL>–更新测试表EMPLOYEESSQL> update employees set FIRST_NAME=LPAD(‘1′,2000,’*’),LAST_NAME=LPAD(‘1′,2000,’*’),EMAIL=LPAD(‘1′,2000,’*’),PHONE_NUMBER=LPAD(‘1′,2000,’*’);107 rows updated.SQL> commit;Commit complete.SQL>–分析测试表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>–查询可知已经产生行迁移SQL> select count(*) from chained_rows where table_name=’EMPLOYEES’; COUNT(*)———-106SQL>–行迁移模拟成功,我把这个过程保存为一个脚本reset_employees.sql:CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR.EMPLOYEES;alter table employees modify FIRST_NAME varchar2(2000);alter table employees modify LAST_NAME varchar2(2000);alter table employees modify EMAIL varchar2(2000);alter table employees modify PHONE_NUMBER varchar2(2000);update employees set FIRST_NAME=LPAD(‘1′,2000,’*’),LAST_NAME=LPAD(‘1′,2000,’*’),EMAIL=LPAD(‘1′,2000,’*’),PHONE_NUMBER=LPAD(‘1′,2000,’*’);–之后模拟行迁移直接执行这个脚本就OK了.
以上就生成了行迁移模拟脚本
第一种消除行迁移的方式:
–准备脚本Solution1.sqlCREATE TABLE employees_tmp tablespace test16k AS SELECT * FROM employees WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = ‘EMPLOYEES’);DELETE FROM employees WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = ‘EMPLOYEES’);INSERT INTO EMPLOYEES SELECT * FROM EMPLOYEES_TMP;DROP TABLE EMPLOYEES_TMP;DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = ‘EMPLOYEES’;COMMIT;–执行脚本Solution1.sqlSQL> @Solution1.sqlTable created.106 rows deleted.106 rows created.Table dropped.106 rows deleted.Commit complete.SQL>–分析测试表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>–查询可知行迁移已经消除SQL> select count(*) from chained_rows where table_name=’EMPLOYEES’; COUNT(*)———-0SQL>
第二种消除行迁移的方式:
–清理测试环境SQL> truncate table chained_rows;Table truncated.SQL> drop table employees;Table dropped.SQL> commit;Commit complete.SQL>–以上清理过程也保存为脚本clear_employees.sql:truncate table chained_rows;drop table employees;commit;–之后的清理工作都会使用这个脚本来进行–模拟行迁移SQL> @reset_employees.sqlTable created.Table altered.Table altered.Table altered.Table altered.107 rows updated.SQL> commit;Commit complete.SQL>–准备脚本Solution2.sqlcreate table employees_tmp tablespace test16k as select * from employees;truncate table employees;insert into employees select * from employees_tmp;drop table employees_tmp;commit;–执行脚本Solution2.sqlSQL> @Solution2.sqlTable created.Table truncated.107 rows created.Table dropped.Commit complete.SQL>–分析测试表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>–查询可知行迁移已经消除SQL> select count(*) from chained_rows where table_name=’EMPLOYEES’; COUNT(*)———-0SQL>
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/530039.html
微信扫一扫 
支付宝扫一扫 
