模拟行链接:
如何模仿行链接?
首先要了解三个语句:
第1句:运行$ORACLE_HOME/rdbms/admin/utlchain.sql 脚本,SQL> @D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\utlchain.sql创建chained_rows表,用于存放发生行迁移
的行的rowid。
第2句:运行analyze table table_name list chained rows into chained_rows; 把产生行迁移的rowid 移动到这个表中。
第3句:运行select * from chained_rows where table_name= 'table_name'; 查看产生的行迁移的rowid。
行链接好模仿,如下:
SQL> create table test (x int primary key ,a char(2000),b char(2000),c char(2000),d
char(2000),e char(2000)) tablespace test2;
test2的块大小是8K,5个char(2000)的字段,这样每行记录约为10k。肯定超过一个块大小。
SQL> insert into test (x) values (1);
SQL> commit;
SQL>
select * from CHAINED_ROWS ;
未选定行
此时还没产生行链接,
SQL> update test set a='test',b='test',c='test',e='test' where x=1;
已更新 1 行。
SQL> commit;
提交完成。
SQL> ANALYZE TABLE test LIST CHAINED ROWS;
表已分析。
SQL> select * from CHAINED_ROWS;
TABLE_NAME HEAD_ROWID
----------------------- --------- ------------------------------
TEST AAAMFaAAHAAACVCAAA
可见产生的是行链接,因为一个块的大小只有8k,而此时这条记录的大小已经约为10k,意味着将跨数据块存储。
那么请问,如何模仿行迁移呢?
下面是一种模仿行迁移的办法:
block的大小为:8k
test@ORCL>create table test (id int,name varchar2(4000),job varchar2(2200),sal varchar(2000) ) pctfree 0 pctused 99;
表已创建。
test@ORCL>insert into test values(1,'dd','xx','sss');
已创建 1 行。
test@ORCL>insert into test values(2,'dx','xx','sss');
已创建 1 行。
test@ORCL>insert into test values(3,'dx','xx','sss');
已创建 1 行。
test@ORCL>insert into test values(4,'dx','xx','sss');
已创建 1 行。
test@ORCL>commit;
提交完成。
test@ORCL>update test set name=RPAD('Z',4000,'Z') where id=4;
已更新 1 行。
test@ORCL>commit;
提交完成。
test@ORCL>select dbms_rowid.rowid_block_number(rowid) as block_number from test;
BLOCK_NUMBER
------------
106
106
106
106
可见所有的记录都是在106号数据块里面。
test@ORCL>ANALYZE TABLE test LIST CHAINED ROWS;
表已分析。
test@ORCL>select * from CHAINED_ROWS;
此时并没有产生行链接或者行迁移。
----执行完上一条update的时候(也就是update test set name=RPAD('Z',4000,'Z') where id=4;),此块剩下的大小不足4k;所以执行下面的update将肯定导致行迁移。
test@ORCL>update test set job=RPAD('J',2200,'J'),sal=RPAD('S',2000,'S') where id=3;
已更新 1 行。
test@ORCL>commit;
提交完成。
test@ORCL>ANALYZE TABLE test LIST CHAINED ROWS;
表已分析。
test@ORCL>select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME
SUBPARTITION_NAME HEAD_ROWID ANALYZE_TI
------------ -------------- ---------------- ------------------ ------------------- ---------------- ----------
TEST TEST N/A AAAOqKAAKAAAABqAAC 25-1月 -10
test@ORCL>select id from test where rowid in (select head_rowid from chained_rows);
ID
-------
3
可见是ID为3的记录产生了行迁移。而此时ID为3的记录所在的块号是不会变化的,尽管是行迁移:
test@ORCL>select dbms_rowid.rowid_block_number(rowid) as block_number from test;
BLOCK_NUMBER
------------
106
106
106
106
-------下面将通过简单的delete insert 的方式来解决这个行迁移的问题:
test@ORCL>create table temp as select * from test where rowid in (select head_rowid from chained_rows);
表已创建。
test@ORCL>delete from test where rowid in (select head_rowid from chained_rows);
已删除 1 行。
test@ORCL>commit;
提交完成。
test@ORCL>select id from test;
ID
----------
1
2
4
test@ORCL>select id from temp;
ID
----------
3
test@ORCL>select * from chained_rows;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TI
------------- ------------------ ---------------- ------------------ ------------------- ------------------ -------
TEST TEST N/A AAAOqKAAKAAAABqAAC 25-1月 -10
而此时chained_rows表里还有之前的行迁移的统计信息,好,我们删了,从新来过:
test@ORCL>ANALYZE TABLE test LIST CHAINED ROWS;
表已分析。
test@ORCL>select * from CHAINED_ROWS;
未选定行
对表从新分析后,可见行迁移已经被干掉了。我们再来看看block number:
test@ORCL>select dbms_rowid.rowid_block_number(rowid) as block_number from test;
BLOCK_NUMBER
------------
106
106
106
107
test@ORCL>select dbms_rowid.rowid_block_number(rowid) as block_number from test
where rowid=(select rowid from test where id=3);
BLOCK_NUMBER
------------
107
可见test表里,id为3的记录已经被从106号数据块里放到了107号数据块里面,而不是像之前的那样:在106号数据块id=3的那条记录里存储了一个指向其他数据块的地址。此时id=3的记录已经被单独放到了107号数据块里。
总结:
行迁移可以通过简单的delete,insert该条数据的方式解决。而行链接的话delete和insert的方式是不能见效的。行链接只有考虑使用更大的数据块,来解决。
总结行清除行迁移的各种方法:
1、传统的清除行迁移的方法
具体步骤如下:
(1)执行$ORACLE_HOME/rdbms/admin目录下的 utlchain.sql脚本创建chained_rows表。
(2)将存在行迁移的表(此处用table_name 代替) 中的产生行迁移的行的rowid 放入到chained_rows表中。
analyze table table_name list chained rows into chained_rows;
(3)将表中行迁移的rowid 放入临时表中保存
create table table_name_temp as select * from table_name where rowid in (select head_rowid from chained_rows where table_name= 'table_name');
(4) 删除表中原来存在的行迁移的记录行
delete table_name where rowid in (select head_rowid from chained_rows where table_name= 'table_name');
(5)从临时表中取出且重新把那些被删除了的数据插入到原来的表中,并删除临时表
insert into table_name select * from table_name_temp;
drop table table_name_temp;
这种传统的清除行迁移(RM)的方法,优点是执行起来的比较简单,容易实现。但是这种算法的缺陷是没有考虑到表关联的情况。但是在真正的数据库应用中,很多表都是和别的表关联在一起的,如果有外键的限制,这样步骤3中的delete是不能删除那条发生行迁移的行的。这种方法在插入和删除行的时候没有disable掉索引,这样导致时间删除和插入时维持索引树的均衡上了,如果记录数多的话,耗时太严重。
2、改进的清除行迁移的方法
(1)执行$ORACLE_HOME/rdbms/admin目录下的 utlchain.sql脚本创建chained_rows表。
(2)禁用所有其他表上关联到此表的所有限制。
(3)将表中行迁移的rowid 放入临时表中保存
create table table_name_temp as select * from table_name where rowid in (select head_rowid from chained_rows where table_name= 'table_name');
(4) 删除表中原来存在的行迁移的记录行
delete table_name where rowid in (select head_rowid from chained_rows where table_name= 'table_name');
(5)从临时表中取出且重新把那些被删除了的数据插入到原来的表中,并删除临时表
insert into table_name select * from table_name_temp;
drop table table_name_temp;
(6)启用所有其他表上关联到此表的所有限制。
下面是一个具体的示例:
select index_name,index_type,table_name from user_indexes where table_name= 'TERMINAL'; —— 查出表对应的索引
select constraint_name,constraint_type,table_name from user_constraints where r_constraint_name = 'PK_TERMINAL_ID';
——查出外键对应的表
alter table 外键表 disable constraint sys_c003200
创建临时表——插入有行连接的数据行——删除原表中有行连接的行。——将临时表中的数据插入原表——删除临时表——启用限制
3、使用toad工具清除行迁移的方法
4、使用emp/imp工具清除行迁移的方法
检查行迁移的方法: 1) 运行$ORACLE_HOME/rdbms/admin/utlchain.sql 2) analyze table table_name list chained rows into CHAINED_ROWS 3) select * from CHAINED_ROWS where table_name='table_name'; 清除的方法: 方法1:create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows); Delete from table_name where rowed in (select head_rowid from chained_rows); Insert into table_name select * from table_name_tmp; 方法2:create table table_name_tmp select * from table_name ; truncate table table_name insert into table_name select * from table_name_tmp 方法3:用exp工具导出表,然后删除这个表,最后用imp工具导入这表 方法4:alter table table_name move tablespace tablespace_name,然后再重新表的索引 上面的4种方法可以用以消除已经存在的行迁移现象,但是行迁移的产生很多情况下时由于PCT_FREE参数设置的太小所导致,所以需要调整PCT_FREE参数的值。 |