Oracle数据库中怎么实现数据行迁移与行链接

本篇内容主要讲解“Oracle数据库中怎么实现数据行迁移与行链接”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Oracle数据库中怎么实现数据行迁移与行链接”吧!

在Oracle数据库中存在两种数据存储现象：行迁移和行链接。这两者现象严重时都会导致数据库查询性能严重低下，处理好数据存储的行链接或者行迁移也是有效提升数据库性能的有效方式。

一、行迁移和行链接的定义：

1.行迁移:
ORACLE一个BLOCK的DEFAULT SIZE是8K,事实上,一个BLOCK不可以存储8K的数据.一个BLOCK可以存储多少数据,由PCTFREE,PCTUSED参数控制(对于以前的手工管理的表空间而言).

PCTFREE：是指BLOCK保留空闲空间的百分比,用于UPDATE。对于已经插入到BLOCK的行而言，后面的UPDATE操作有可能使行的长度增加；PCTFREE就是用于容纳增加的那部分长度而保留的空闲空间。如果UPDATE时PCTFREE再也不能够容纳行增加的长度，则ORACLE会将整个行迁移到一个新的BLOCK，行的ROWID保留(不是太明白为什么ORACLE不改变ROWID)，原来的BLOCK有一个指针指向ROW存放的新BLOCK。这就是行迁移。

可见，行迁移是由于UPDATE操作所导致。从字面上理解，所谓迁移，肯定先有存在这一行，才能叫着迁移.

2.行链接:
是指一个BLOCK不能容纳一行(行的长度太大),而必须将此行存放于几个BLOCK.行链接一般是在Insert时产生的.一个BLOCK能否用于insert是由PCTUSED控制.

PCTUSED：是指BLOCK用于INSERT的百分比。对于INSERT操作，BLOCK可用于容纳新行的最大空间为Blocksize-pctfree-overhead.当BLOCK数据存储已高于PCTUSED，ORACLE会将该块从自由链表中移除，直到该块已使用空间降到PCTUSED以下，才会再次将此块重新加入到Freelist(这是ORACLE以前手工管理的表空间管理空闲块的原理,现在ORACLE推荐使用ASSM).

对表分析后检查表中行迁移的情况：

select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT from user_tables where table_name='EMP';

（其中字段CHAIN_CNT显示的就是行迁移或者行链接的表中的行数）

二、行迁移与行链接的分析检测：

检测表中是否存在行迁移或者行链接需要对表进行分析：
表分析的方法有两种：

1, analyze table emp compute statistics;2, exec dbms_stats.gather_schema_stats('scott','emp');   --前者为用户名，后者为表名

但是ORACLE的dbms_stats.gather_schema_stats只会收集优化器统计信息,不会检测表的记录是否存在行迁移和行链接，因此要分析行链接或者行迁移必须用analyse命令。

行迁移和行链接的检测：

select b.NAME,a.VALUE from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'table fetch continued row';

当有返回值时，可以知道表的数据存在行迁移和行链接。
行迁移和行链接的清除：
能过REBUILD数据来清除行迁移：

create table MM_PM_temp as select * from MM_PM; truncate table MM_PM; insert into MM_PM select * from MM_PM_temp

再重新分析表：

analyze table MM_PM compute statistics;

分析过后再查看：

select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %" from user_tables t where t.chain_cnt > 0;

如果该表的chain_cnt变为0时，表示原先的chain_cnt全部是行迁移，而不是行链接。
如果REBUILD数据后chain_cnt变少，但还大于0，则可以证明，这个表即包含行迁移，又包含真正的行链接。

事实证明，行迁移是可以通过REBUILD数据和增加PCTFREE%来清除和减少发生频率的。

注意，对于ASSM，PCTUSED，FREELIST，FREELIST GROUPS参数会被忽略。
但对于真正的行链接，只能通过将表移植到大的BLOCSIZE的表空间上。

例如：

创建一个16K的表空间：

CREATE TABLESPACE LARGETBS BLOCKSIZE 16 K LOGGING DATAFILE '/data/app/oracle/oradata/ora33/LARGETBS_01.dbf' SIZE 64M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

在创建的时候报了个ORA-的错，原因我们没有设定16的DB_Buffer_cache,我们设定一下：

 alter system set db_16k_cache_size=34603008;

将表MOVE到16K的表空间：

 alter table class="lazy" data-src_CS move tablespace LARGETBS;
 alter table MM_PM move tablespace LARGETBS;

由于进行了迁移，表的索引会失效，所以我们要REBUILD索引：

 alter index PK_class="lazy" data-src_CS rebuild; alter index PK_MM_PM rebuild;

再重新分析：

 analyze table class="lazy" data-src_CS compute statistics; analyze table MM_PM compute statistics;

重新查询：

 select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %" from user_tables t where t.chain_cnt > 0 order by t.table_name;

发现，这些表都没有ROW CHAIN了。可见，MOVE到16K的表空间可以清除ROW CHAIN。

三、消除迁移的方法:

一：生成一张表(chained_rows),保存迁移的行的rowid

@?/rdbms/admin/utlchain

使用分析命令将产生迁移的行的rowid插入到chained_rows表:

analyze table test01 list chained rows into chained_rows;create table tmp as select * from test01 where rowid in (select head_rowid from chained_rows);delete test01 where rowid in (select head_rowid from chained_rows);insert into test01 select * from tmp;drop table tmp purge;

再分析表：

analyze table test01 compute statistics;

再查看字典中的统计信息：

select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT from user_tables where table_name='TEST01';

二、将存在行迁移的表用导出工具导出数据库，将原表truncate后，再讲数据重新导入。

到此，相信大家对“Oracle数据库中怎么实现数据行迁移与行链接”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！