练习一：整理行迁移
任务  描述
一、准备测试环境  1、创建测试表
SQL> create table ora1(id number);
表已创建。
2、创建存储过程，向表中插入10000条记录
SQL> create or replace procedure sp_insert_ora1
2 is
3 begin
4 for i in 1..10000 loop
5 insert into ora1 values(i);
6 end loop;
7* end;
SQL> /
过程已创建。
3、执行存储过程
SQL> exec sp_insert_ora1
PL/SQL 过程已成功完成。
4、要察看数据字典中的信息，一定要先更新它。
SQL> analyze table ora1 estimate statistics;
表已分析。
5、察看ora1表使用了多少个块
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------20 
6、察看表中有多少行，并且行迁移数量
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
二、导致行迁移  1、插入一列，更新数据，产生行迁移情况。并察看相关信息
SQL> alter table ora1 add name varchar2(100);
表已更改。
2、查看表结构
SQL> desc ora1
名称  空?  类型 
----------------------------------------- ------------------------------------ID NUMBER
NAME    VARCHAR2(100)
3、执行update操作，肯定会引起行迁移
SQL> update ora1 set name =to_char(id)||'ddddddddddd';
已更新10000行。
4、没有更新数据字典中的信息，所以没有变化。
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
5、重新更新数据字典信息
SQL> analyze table ora1 estimate statistics;
表已分析。
6、块的个数增加了。
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
---------- 
55 
7、发现10000条纪录中，有9051条发生了行迁移
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT   
---------- ----------10000 9051 
8、AVG_SPACE每个块中的平均字节。
SQL> select 
num_rows,blocks,empty_blocks ,avg_space,chain_cnt,avg_row_len from d
ba_tables where table_name='ORA1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------10000 55 0 783 9051 28
三、使用临时表整
理行迁移
1、创建chained_rows ，使用这个表可以帮助我们察看哪些纪录有行迁移
SQL> @?\rdbms\admin\utlchain.sql
表已创建。
2、将哪行有行迁移，写入到CHAINED_ROWS中 
SQL> ANALYZE TABLE ORA1 LIST CHAINED ROWS;
表已分析。
3、察看哪些行有迁移情况
SQL> SELECT owner_name, table_name, head_rowid FROM chained_rows WHERE 
table_name = 'ORA1' ;
OWNER_NAME TABLE_NAME 
HEAD_ROWID 
------------------------------ ------------------------------------------------A ORA1 
AAAAzNAABAAABt8ABL
A    ORA1 
AAAAzNAABAAABt8ABM
A ORA1 
AAAAzNAABAAABt8ABN
…………
…………省略很多。
已选择9051行。
4、将有行迁移的数据复制到其他表，然后整理行迁移
SQL> CREATE table tmp
2 as
3 select * from ora1
4 where rowid in(select HEAD_ROWID from CHAINED_ROWS);
表已创建。
5、删除有行迁移的记录
SQL> delete from ora1
2 where rowid in(select HEAD_ROWID from CHAINED_ROWS);
已删除9051行。
6、将行迁移数据重新写回表中
SQL> insert into ora1 select * from tmp;
已创建9051行。
7、提交
SQL> commit;
提交完成。
8、删除临时表
SQL> DROP table tmp;
9、重新察看一下，发现行迁移没了
SQL> analyze table ora1 estimate statistics;
表已分析。
10、查看行迁移相关信息
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
11、查看块的使用信息
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------55   
四、使用 MOVE
方法整理碎片
1、删除表
SQL> drop table ora1;
表已丢弃。
2、创建测试表
SQL> create table ora1(id number);
表已创建。
3、执行存储过程
SQL> exec sp_insert_ora1
PL/SQL 过程已成功完成。
4、要察看数据字典中的信息，一定要先更新它。
SQL> analyze table ora1 estimate statistics;
表已分析。
5、察看ora1表使用了多少个块
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
---------- 
20
6、察看表中有多少行，并且行迁移数量
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT   
---------- ----------10000 0 
7、插入一列，更新数据，产生行迁移情况。并察看相关信息 
SQL> alter table ora1 add name varchar2(100);
表已更改。
8、执行update操作，肯定会引起行迁移
SQL> update ora1 set name =to_char(id)||'ddddddddddd';
已更新10000行。
9、没有更新数据字典中的信息，所以没有变化。
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ---------- 
10000 0 
10、重新更新数据字典信息
SQL> analyze table ora1 estimate statistics;
表已分析。
11、块的个数增加了。
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------55 
12、发现10000条纪录中，有9051条发生了行迁移
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ---------- 
10000 9051
13、将表移动，移动时就自动整理迁移和碎片，
SQL> alter table ora1 move ;
表已更改。
14、分析表，统计信息
SQL> analyze table ora1 estimate statistics;
表已分析。
15、重新察看一下，发现行迁移没了
SQL> select 
num_rows,blocks,empty_blocks ,avg_space,chain_cnt,avg_row_len from 
dba_tables where table_name='ORA1';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- ----------- 
10000 35 12 996 0 23
五、使用pctfree
参数，可以一定程
度减少行迁移
1、删除表
SQL> drop table ora1;
表已丢弃。
2、创建测试表
SQL> create table ora1(id number) pctfree 40;
表已创建。
3、执行存储过程
SQL> exec sp_insert_ora1
PL/SQL 过程已成功完成。
4、要察看数据字典中的信息，一定要先更新它。
SQL> analyze table ora1 estimate statistics;
表已分析。
5、察看ora1表使用了多少个块(块的数量前面多了些)
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------25
6、察看表中有多少行，并且行迁移数量
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000 0 
7、插入一列，更新数据，产生行迁移情况。并察看相关信息
SQL> alter table ora1 add name varchar2(100);
表已更改。
8、执行update操作，肯定会引起行迁移
SQL> update ora1 set name =to_char(id)||'ddddddddddd';
已更新10000行。
9、没有更新数据字典中的信息，所以没有变化。
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT   
---------- ----------10000 0 
10、重新更新数据字典信息 
SQL> analyze table ora1 estimate statistics;
表已分析。
11、块的个数增加了。
SQL> select blocks from dba_tables where table_name='ORA1';
BLOCKS 
----------55 
12、行迁移的数量明显少多了,原来是9051个行迁移
SQL> select num_rows,chain_cnt from dba_tables where table_name ='ORA1';
NUM_ROWS CHAIN_CNT 
---------- ----------10000    4603
练习二、整理索引碎片
任务  描述
一、准备测试数据  1、创建表、插入记录和创建索引
SQL> create table ind (id number,name varchar2(100));
表已创建。
2、创建存储过程，向表中插入10000条记录
SQL> create or replace procedure sp_insert_ind
2 is
3 begin
4 for i in 1..10000 loop
5 insert into ind values(i,to_char(i)||'aaaaaaaaaa');
6 end loop;
7 end;
8 /
过程已创建。
3、执行存储过程
SQL> exec sp_insert_ind
PL/SQL 过程已成功完成。
4、创建索引
SQL> create index ind_id_idx on ind(id);
索引已创建。
5、收集信息，没有更新数据字典，所以没有信息
SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from 
index_stats;
未选定行
6、更新数据字典
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
7、查看索引中碎片情况
SQL> select lf_rowsdel_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  ---------------10000 0 
二、产生碎片  1、更新表中1000行记录，这时会更新索引树
SQL> update ind set id=id+1 where id>9000;
已更新1000行。
2、分析索引信息
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、查看索引碎片信息
--总共行的数量增加了1000行，并且标示为删除了1000行记录
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  -----------11000 1000 
三、使用 rebuild
方法重建索引
1、重建索引
SQL> alter index ind_id_idx rebuild;
索引已更改。
2、分析索引信息
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、查看索引碎片信息，发现索引中碎片没有了
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
---------- -----------10000 0 
四、产生碎片  1、删除表中1000行记录，这时会更新索引树 
SQL> delete from ind where id>9000;
已删除1000行。
2、分析索引信息
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、查看索引碎片信息
--总共行的数量增加了1000行，并且标示为删除了1000行记录
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  -----------10000 1000 
五、使用 coalesce
方法重建索引
1、重建索引
SQL> alter index ind_id_idx rebuild online;
索引已更改。
2、分析索引信息
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、查看索引碎片信息，发现索引中碎片没有了
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
---------- -----------9000 0 
六、产生碎片  1、删除表中1000行记录，这时会更新索引树
SQL> delete from ind where id>8000;
已删除1000行。
2、分析索引信息
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、查看索引碎片信息
--总共行的数量增加了1000行，并且标示为删除了1000行记录
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
----------  -----------9000 1000 
七、使用 coalesce 1、重建索引 
方法重建索引  SQL> alter index ind_id_idx coalesce;
索引已更改。
2、分析索引信息
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;
索引已分析
3、查看索引碎片信息，发现索引中碎片没有了
SQL> select lf_rows,del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS 
---------- ----------- 
8000 0