Oracle 行迁移和行链接
一、行迁移
1.1、行迁移概念
当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。
成因：当行被update时，如果update更新的行大于数据块的pctfree值，就需要申请第2个块，从而形成迁移。
后果：导致应用需要访问更多的数据块，性能下降。
预防：1.将数据块的pctfree调大；
2.针对表空间扩大数据块的大小。
检查：analyze table 表名 validate structure cascade into chained_rows;
2.1、实例：
实验说明：
(以EMPLOYEES表为例，如果涉及到该表有主键，并且有别的表的外键REFERENCE关联到本表，必须要执行步骤2和步骤7，否则不必执行)；
1． 执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。
2. 禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES表有主键PK_EMPLOYEES_ID，假想test表有外键f_employees_id关联reference到employees表）。
select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES';
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_ID';
alter table test disable constraint f_employees_id;
3． 将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。
4． 将表中的行迁移的row id放入临时表中保存。
5． 删除原来表中存在的行迁移的记录行。
6． 从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。
7． 启用所有其它表上关联到此表上的所有限制。
alter table test enable constraint f_employees_id;
此外还可以采用move和exp/imp的方式（特别注意move会导致索引失效，需要重建索引）。
1 ----创建实验表---- 2 SQL> DROP TABLE EMPLOYEES PURGE; 3 DROP TABLE EMPLOYEES PURGE 4 * 5 第 1 行出现错误: 6 ORA-00942: 表或视图不存在 7 8 9 SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ; 10 11 表已创建。 12 13 SQL> desc EMPLOYEES; 14 名称 是否为空? 类型 15 ----------------------------------------- -------- ---------------------------- 16 EMPLOYEE_ID NUMBER(6) 17 FIRST_NAME VARCHAR2(20) 18 LAST_NAME NOT NULL VARCHAR2(25) 19 EMAIL NOT NULL VARCHAR2(25) 20 PHONE_NUMBER VARCHAR2(20) 21 HIRE_DATE NOT NULL DATE 22 JOB_ID NOT NULL VARCHAR2(10) 23 SALARY NUMBER(8,2) 24 COMMISSION_PCT NUMBER(2,2) 25 MANAGER_ID NUMBER(6) 26 DEPARTMENT_ID NUMBER(4) 27 28 SQL> create index idx_emp_id on employees(employee_id); 29 30 索引已创建。 31 32 ---扩大字段---- 33 SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(1000); 34 35 表已更改。 36 37 SQL> alter table EMPLOYEES modify LAST_NAME VARCHAR2(1000); 38 39 表已更改。 40 41 SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(1000); 42 43 表已更改。 44 45 SQL> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(1000); 46 47 表已更改。 48 49 SQL> desc employees; 50 名称 是否为空? 类型 51 ----------------------------------------- -------- ---------------------------- 52 EMPLOYEE_ID NUMBER(6) 53 FIRST_NAME VARCHAR2(1000) 54 LAST_NAME NOT NULL VARCHAR2(1000) 55 EMAIL NOT NULL VARCHAR2(1000) 56 PHONE_NUMBER VARCHAR2(1000) 57 HIRE_DATE NOT NULL DATE 58 JOB_ID NOT NULL VARCHAR2(10) 59 SALARY NUMBER(8,2) 60 COMMISSION_PCT NUMBER(2,2) 61 MANAGER_ID NUMBER(6) 62 DEPARTMENT_ID NUMBER(4) 63 64 ----更新表---- 65 SQL> UPDATE EMPLOYEES 66 2 SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'), 67 3 PHONE_NUMBER = LPAD('1', 1000, '*'); 68 69 已更新107行。 70 71 SQL> commit; 72 73 提交完成。 74 75 ----行迁移优化前，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）---- 76 SQL> set autotrace traceonly stat 77 SQL> set linesize 1000 78 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 79 80 已选择107行。 81 82 83 统计信息 84 ---------------------------------------------------------- 85 152 recursive calls 86 0 db block gets 87 310 consistent gets 88 0 physical reads 89 0 redo size 90 437664 bytes sent via SQL*Net to client 91 492 bytes received via SQL*Net from client 92 9 SQL*Net roundtrips to/from client 93 0 sorts (memory) 94 0 sorts (disk) 95 107 rows processed 96 97 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 98 99 已选择107行。 100 101 102 统计信息 103 ---------------------------------------------------------- 104 0 recursive calls 105 0 db block gets 106 219 consistent gets 107 0 physical reads 108 0 redo size 109 437664 bytes sent via SQL*Net to client 110 492 bytes received via SQL*Net from client 111 9 SQL*Net roundtrips to/from client 112 0 sorts (memory) 113 0 sorts (disk) 114 107 rows processed 115 116 SQL> set autotrace off 117 118 ----- 发现存在行迁移的方法 119 --首先建chaind_rows相关表，这是必需的步骤 120 SQL> drop table chained_rows purge; 121 drop table chained_rows purge 122 * 123 第 1 行出现错误: 124 ORA-00942: 表或视图不存在 125 126 127 SQL> @?/rdbms/admin/utlchain.sql 128 129 表已创建。 130 ----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中 131 132 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 133 134 表已分析。 135 136 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; 137 138 COUNT(*) 139 ---------- 140 105 141 ---以下方法可以去除行迁移 142 SQL> drop table EMPLOYEES_TMP; 143 drop table EMPLOYEES_TMP 144 * 145 第 1 行出现错误: 146 ORA-00942: 表或视图不存在 147 148 149 SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows); 150 151 表已创建。 152 153 SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows); 154 155 已删除105行。 156 157 SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP; 158 159 已创建105行。 160 161 SQL> delete from chained_rows ; 162 163 已删除105行。 164 165 SQL> commit; 166 167 提交完成。 168 169 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 170 171 表已分析。 172 173 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; 174 175 COUNT(*) 176 ---------- 177 0 178 179 --这时的取值一定为0，用这种方法做行迁移消除，肯定是没问题的！ 180 181 ---行迁移优化后，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可） 182 SET AUTOTRACE traceonly statistics 183 SQL> set linesize 1000 184 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 185 186 已选择107行。 187 188 189 统计信息 190 ---------------------------------------------------------- 191 0 recursive calls 192 0 db block gets 193 116 consistent gets 194 0 physical reads 195 0 redo size 196 437034 bytes sent via SQL*Net to client 197 492 bytes received via SQL*Net from client 198 9 SQL*Net roundtrips to/from client 199 0 sorts (memory) 200 0 sorts (disk) 201 107 rows processed
二、行链接
2.1、行链接概念
当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle数据块大小，而你需要插入一行数据是8k，Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。
产生原因：当一行数据大于一个数据块，ORACLE会同时分配两个数据块，并在第一个块上登记第二个块的地址，从而形成行链接。
预防方法：针对表空间扩大数据块大小。
检查：analyze table 表名 validate structure cascade into chained_rows;
1 ----建表---- 2 SQL> DROP TABLE EMPLOYEES PURGE; 3 4 表已删除。 5 6 SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ; 7 8 表已创建。 9 10 SQL> set linesize 80; 11 SQL> desc EMPLOYEES; 12 名称 是否为空? 类型 13 ----------------------------------------- -------- ---------------------------- 14 EMPLOYEE_ID NUMBER(6) 15 FIRST_NAME VARCHAR2(20) 16 LAST_NAME NOT NULL VARCHAR2(25) 17 EMAIL NOT NULL VARCHAR2(25) 18 PHONE_NUMBER VARCHAR2(20) 19 HIRE_DATE NOT NULL DATE 20 JOB_ID NOT NULL VARCHAR2(10) 21 SALARY NUMBER(8,2) 22 COMMISSION_PCT NUMBER(2,2) 23 MANAGER_ID NUMBER(6) 24 DEPARTMENT_ID NUMBER(4) 25 26 SQL> create index idx_emp_id on employees(employee_id); 27 28 索引已创建。 29 ----扩大字段---- 30 SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000); 31 32 表已更改。 33 34 SQL> alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000); 35 36 表已更改。 37 38 SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(2000); 39 40 表已更改。 41 42 SQL> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000); 43 44 表已更改。 45 ----更新表---- 46 UPDATE EMPLOYEES 47 SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'), 48 PHONE_NUMBER = LPAD('1', 2000, '*'); 49 COMMIT; 50 51 已更新107行。 52 53 SQL> 54 提交完成。 55 56 -----行链接移优化前，先看看该语句逻辑读情况 57 SET AUTOTRACE traceonly 58 SQL> set linesize 1000 59 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 60 统计信息 61 ---------------------------------------------------------- 62 153 recursive calls 63 1 db block gets 64 415 consistent gets 65 0 physical reads 66 176 redo size 67 868529 bytes sent via SQL*Net to client 68 492 bytes received via SQL*Net from client 69 9 SQL*Net roundtrips to/from client 70 0 sorts (memory) 71 0 sorts (disk) 72 107 rows processed 73 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 74 75 已选择107行。 76 77 78 统计信息 79 ---------------------------------------------------------- 80 7 recursive calls 81 0 db block gets 82 397 consistent gets 83 0 physical reads 84 0 redo size 85 868529 bytes sent via SQL*Net to client 86 492 bytes received via SQL*Net from client 87 9 SQL*Net roundtrips to/from client 88 0 sorts (memory) 89 0 sorts (disk) 90 107 rows processed 91 92 SQL> set autotrace off 93 --------- 发现存在行链接的方法 94 --首先建chaind_rows相关表，这是必需的步骤 95 SQL> drop table chained_rows purge; 96 97 表已删除。 98 99 SQL> @?/rdbms/admin/utlchain.sql 100 101 表已创建。 102 103 ----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中 104 105 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 106 107 表已分析。 108 109 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; 110 111 COUNT(*) 112 ---------- 113 107 114 115 ---用消除行迁移的方法根本无法消除行链接!!! 116 117 SQL> drop table EMPLOYEES_TMP; 118 119 表已删除。 120 121 SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows); 122 123 表已创建。 124 125 SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows); 126 127 已删除107行。 128 129 SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP; 130 131 已创建107行。 132 133 SQL> delete from chained_rows ; 134 135 已删除107行。 136 137 SQL> commit; 138 139 提交完成。 140 --发现用消除行迁移的方法根本无法消除行链接! 141 SQL> analyze table EMPLOYEES list chained rows into chained_rows; 142 143 表已分析。 144 145 SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; 146 147 COUNT(*) 148 ---------- 149 107 150 151 SQL> SET AUTOTRACE traceonly stat 152 SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0; 153 154 已选择107行。 155 156 157 统计信息 158 ---------------------------------------------------------- 159 0 recursive calls 160 0 db block gets 161 223 consistent gets 162 0 physical reads 163 0 redo size 164 867923 bytes sent via SQL*Net to client 165 492 bytes received via SQL*Net from client 166 9 SQL*Net roundtrips to/from client 167 0 sorts (memory) 168 0 sorts (disk) 169 107 rows processed 170 171 ---启动大小为32K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K) 172 --行链接只有通过加大BLOCK块的方式才可以避免，如下： 173 create tablespace TBS_JACK_16k 174 blocksize 16k 175 datafile '/u01/app/oracle/oradata/orcl/TBS_JACK_32K_01.dbf' size 100m 176 autoextend on 177 extent management local 178 6 segment space management auto; 179 create tablespace TBS_JACK_16k 180 * 181 第 1 行出现错误: 182 ORA-29339: 表空间块大小 16384 与配置的块大小不匹配 183 ------------------ORA-29339报错解决办法！ 184 185 ----解决问题后再次创建表空间---- 186 SQL> / 187 188 表空间已创建。 189 190 SQL> DROP TABLE EMPLOYEES_BK PURGE; 191 DROP TABLE EMPLOYEES_BK PURGE 192 * 193 第 1 行出现错误: 194 ORA-00942: 表或视图不存在 195 SQL> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_JACK_16K AS SELECT * FROM EMPLOYEES; 196 197 表已创建。 198 199 SQL> delete from chained_rows ; 200 201 已删除107行。 202 SQL> analyze table EMPLOYEES_BK list chained rows into chained_rows; 203 204 表已分析。 205 206 SQL> select count(*) from chained_rows where table_name='EMPLOYEES_BK'; 207 208 COUNT(*) 209 ---------- 210 0
