Oracle 收缩表move与shrink 效果测试

Oracle 10G 测试move与shrink

测试两个表 gps_ht_9612与 gps_ht_9613 两表并未建索引。

SQL> select * from gps_sq where sq<11;

Executed in 0.113 seconds

清空两表内容

对两个表作表分析统计

查看两表数据块信息都是样的8K

Executed in 0.106 seconds

将数据导入两个表中。

SQL> insert into gps_ht_9610 select * from gps_ht_9611;
165593 rows inserted
Executed in 1.5 seconds

SQL> insert into gps_ht_9611 select * from gps_ht_9610;
705054 rows inserted
Executed in 7.338 seconds

SQL> commit;
Commit complete
Executed in 0.031 seconds

SQL> insert into gps_ht_9610 select * from gps_ht_9611;
870647 rows inserted
Executed in 7.846 seconds

SQL> commit;
Commit complete
Executed in 0.063 seconds

SQL> insert into gps_ht_9612 select * from gps_ht_9610;
1575701 rows inserted
Executed in 31.26 seconds

SQL> insert into gps_ht_9613 select * from gps_ht_9610;
1575701 rows inserted
Executed in 36.477 seconds

SQL> commit;
Commit complete
Executed in 0.042 seconds

两个表都导入了1575701行数据

并重新分析了表。

SQL> analyze table gps_ht_9613 compute statistics;
Table analyzed
Executed in 20.087 seconds

SQL> analyze table gps_ht_9612 compute statistics;
Table analyzed
Executed in 23.117 seconds

查看空块信息，两个表，从同一个表中导入相同的数据，竟然空块相差很多。

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9612';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9612                         17617          687
Executed in 0.041 seconds

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9613';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9613                         17618          430
Executed in 0.05 seconds

对两个表进行move与shrink 收缩表
SQL> alter table gps_ht_9612 enable row movement;
Table altered
Executed in 0.461 seconds

SQL> alter table gps_ht_9613 enable row movement;
Table altered
Executed in 0.06 seconds

SQL> alter table GPS_HT_9612 move;
Table altered
Executed in 20.018 seconds

SQL> alter table GPS_HT_9613 shrink space cascade;
Table altered
Executed in 1.207 seconds

重新分析表信息：

SQL> analyze table gps_ht_9612 compute statistics;
Table analyzed
Executed in 21.726 seconds

SQL> analyze table gps_ht_9613 compute statistics;
Table analyzed
Executed in 20.508 seconds

发现如果表数据没有使用delete删除部分数据时,shrink表时间,空块会减少，而move时，表空块反而会增加。

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9613';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9613                         17506          174
Executed in 0.032 seconds

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9612';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9612                         17882          934
Executed in 0.042 seconds

查看两个表占用空间大小

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GPS_HT_9612';
SUM(BYTES)/1024/1024
--------------------
                 147
Executed in 1.461 seconds

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GPS_HT_9613';
SUM(BYTES)/1024/1024
--------------------
             138.125
Executed in 0.389 seconds

分别对两个表进行move与shrink 收缩表空间

SQL> alter table GPS_HT_9613 shrink space cascade;
Table altered
Executed in 66.555 seconds

SQL> alter table GPS_HT_9612 move;
Table altered
Executed in 4.381 seconds

重新对表进行分析统计信息

SQL> analyze table gps_ht_9612 compute statistics;
Table analyzed
Executed in 3.984 seconds

SQL> analyze table gps_ht_9613 compute statistics;
Table analyzed
Executed in 3.935 seconds

查看两表空块区别

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9613';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9613                          2933           59
Executed in 0.08 seconds

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9612';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9612                          3156           44
Executed in 0.094 seconds

再查看两个表的大小

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GPS_HT_9612';
SUM(BYTES)/1024/1024
--------------------
                  25
Executed in 0.577 seconds

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GPS_HT_9613';
SUM(BYTES)/1024/1024
--------------------
              23.375
Executed in 0.32 seconds

再对GPS_HT_9612 进行 shrink操作

SQL> alter table GPS_HT_9612 shrink space cascade;
Table altered
Executed in 0.068 seconds

查看GPS_HT_9612 表信息

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GPS_HT_9612';
SUM(BYTES)/1024/1024
--------------------
             24.6875
Executed in 0.134 seconds

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='GPS_HT_9612';
TABLE_NAME                         BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
GPS_HT_9612                          3156           44
Executed in 0.056 seconds

以上可以看出两个表分别进行move与shrink操作，有什么效果区别了。