1、通过user_table_statistics 查看行迁移数量
准备：
analyze table MPI_PSYCOMPARETEMP compute statistics;
收集统计信息
select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tab_statistics where TABLE_NAME='MPI_PSYCOMPARETEMP'
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
----------   ---------- ------------    ---------- ----------   -----------
24199     370 14    1693    7491   76
CHAIN_CNT 为：7491
AVG_SPACE 为：1693
select * from MPI_PSYCOMPARETEMP
Plan
SELECT STATEMENT ALL_ROWSCost: 103 Bytes: 1,669,731 Cardinality: 24,199
1 TABLE ACCESS FULL TABLE HZWSJ.MPI_PSYCOMPARETEMP Cost: 103 Bytes: 1,669,731 Cardinality: 24,199

2、create table MPI_PSYCOMPARETEMPBAK AS SELECT  * FROM MPI_PSYCOMPARETEMP
analyze table MPI_PSYCOMPARETEMPBAK compute statistics;
select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tab_statistics where TABLE_NAME='MPI_PSYCOMPARETEMPBAK'

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
----------   ----------    ------------ ---------- ----------    -----------
24199     270    114   854    0    74
可以看出新创建的表分配的块为270个比原先的少100个，并且空余块 114 比原先多了100 平均空间少了1693 变为854 行迁移为零，平均行长度为74 少2.
接下来我们看
select * from MPI_PSYCOMPARETEMPBAK
Plan
SELECT STATEMENT ALL_ROWSCost: 76 Bytes: 1,669,731 Cardinality: 24,199
1 TABLE ACCESS FULL TABLE HZWSJ.MPI_PSYCOMPARETEMPBAK Cost: 76 Bytes: 1,669,731 Cardinality: 24,199

没什么变化。只不过cost 减少了而已。读取的数据没有减少。--- 这里的读取的块应该是变少了但是bytes 为何一样？

未完。

以上是通过toad的执行计划得出的。下面是通过set autotrace traceonly 来看的。
alter system flush buffer_cache;
SELECT * FROM MPI_PSYCOMPARETEMP;
1938 consistent gets
342 physical reads

select * from MPI_PSYCOMPARETEMPbak ;

1861 consistent gets
257 physical reads
看出来真正还是发生变化的，有误解
| Bytes |
| 1630K|
| 1630K|

ITPub博客

行迁移测试

最新文章