oracle高水位

在做数据清理的时候, 需要删除大量数据, 但是删除之后发现, sql的执行效率并没有变块. 后经过研究发现这是由于oracle的高水位原因造成的.

高水位

在数据库表刚建立的时候, 由于没有任何数据, 所以这个时候水位线是空的, 也就是说HWM为最低值. 当插入了数据以后, 高水位线就会上涨, 但是这里有一个特性, 就是如果你采用delete语句删除数据的话, 数据虽然被删除了, 但是高水位线却没有降低, 还是你删除数据以前那么高的水位. 也就是说, 这条高水位线在日常的增删操作中只会上涨, 不会下跌.

HWM对数据库的操作有如下影响:

1. 全表扫描通常要读出直到HWM标记的所有的属于该表的数据库块, 即使该表中没有任何数据.
2. 即使HWM以下有空闲的数据库块, 键入在插入数据时使用了append关键字, 则在插入时使用HWM以上的数据块,此时HWM会自动增大.

如何知道一个表的HWM

首先对表进行分析:

ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
例: analyze table promo_used compute statistics;

select blocks, empty_blocks, num_rows from user_tables where table_name = <tablename>;

注: blocks列代表该表中曾经使用过的数据库块的数目, 即水线.
empty_blocks列代表分配给该表, 但是在水线以上的数据库块, 即从来没有使用的数据块.
让我们以一个有28672行的big_emp表为例进行说明:

1: select segment_name, segment_type, blocks from dba_segments where segment_name = ‘big_emp’;

查询结果如下:

segment_name	segment_type	blocks
Big_emp	table	1024

2:	analyze table big_emp estimate statistics;

3: select table_name, num_rows, blocks, empty_blocks from user_tables where table_name=’big_emp’;

查询结果如下:

table_name	num_rows	blocks	empty_blocks
Big_emp	28672	700	323

注意: blocks + empty_blocks(700+323)比dba_segment.blocks少1个数据库块, 这是因为有一个数据库块被保留用作segment header. Dba_segment.blocks表示分配给这个表的所有的数据库块的数目. user_table.blocks表示已使用过的数据库块的数目.

修正oracle表的高水位线

在oracle中,执行对表的删除操作不会降低该表的高水位线.而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块.如果在执行删除操作后不降低高水位线标记, 则将导致查询语句的性能低下

方式一

alter table table_name move;

该操作后, 原索引失效,需要重建索引. (为什么move操作后索引会失效? 这是由于move操作后, 会对该表的未使用过的数据块进行回收,并有可能会移动之前的数据块. 例: move操作前, 该表有A,B,C,D,E,F五个数据块, 其中B,D两个数据未使用, 那么move操作后, 可能将E,F数据块的数据移动到了B,D块, 然后释放了E,F块, 所以之前的索引指向就失效了

方式二

 alter table table_name shrink space;

此命令为oracle 10g新增功能, 再执行该命令之前必须允许移动

 alter table table_name enable row movement;

再执行该命令之后必须修改不允许移动,复位到原来状态

alter table table_name disable row movement;

方式三

alter table table_name deallocate unused;

方式四

尽量truncate吧

修改后再查看高水位

4: select table_name, num_rows, blocks, empty_blocks from user_tables where table_name=’big_emp’;

查询结果如下:

table_name	num_rows	blocks	empty_blocks
Big_emp	28672	700	0

简书：https://www.jianshu.com/u/0278602aea1d
CSDN：https://blog.csdn.net/u012387141
特别感谢：莫幽夜