oracle高水位线的一次模拟测试

经常会遇到一个表，其中只有几条数据，结果查询一下表记录总数，发现特别的慢。这通常是由于oracle高水位线的问题引起的，见[1]。这样的表在实际应用中也不枚胜举，比如短信发送表，一次大批量发送了500万条短信，发完后表的记录全被删除（是delete，而不是truncate掉），之后查询该表会非常慢。原因在于delete操作并不会回收表被删除的空间，导致全表扫描时仍旧需要扫描那些记录已经全部被删除的块，导致速度和delete操作前没有变化。如果调用alter table XXX shrink SPACE收缩一下表，问题即解决。

 

本文即建了一个测试表（表有1个varchar2型的字段，长度为1024个byte）模拟了这个过程。先插入50万条记录，故表占用总空间约为500M（=500,000*1024）。之后进行delete操作，只保留其中一条记录。之后查询速度和delete前一样，收缩一下表之后，几个毫秒即即可返回结果。

1. -- 创建测试表
2. CREATE TABLE lcm_test(c1 INT, c2 VARCHAR2(1024));
3. --TRUNCATE TABLE lcm_test;
5. -- 统计表记录行数（没有任何索引，采用全表扫描）
6. SELECT COUNT(*) FROM lcm_test;
8. -- 收集统计信息
9. ANALYZE TABLE lcm_test ESTIMATE STATISTICS;
11. --察看表统计信息
12. SELECT * 
13. --SELECT COUNT(*)
14. FROM all_tables 
15. WHERE table_name = 'LCM_TEST';
17. -- 插入测试数据
18. DECLARE
19. BEGIN
20.     FOR i IN 1 .. 500000 LOOP
21.         INSERT INTO lcm_test(c1, c2)
22.         VALUES (i, lpad('a', 1024, 'a'));
24.         IF MOD(i, 1000) = 0 THEN
25.             COMMIT;
26.         END IF;
27.     END LOOP;
28.     COMMIT;
29. END;
31. -- 只保留一条记录
32. DELETE FROM lcm_test WHERE c1 > 1;
34. COMMIT;
36. -- 统计表记录行数（没有任何索引，采用全表扫描）
37. -- 由于delete并不会回收空间，故仍旧需要扫描记录已经全被删除的块
38. -- 此时若收集lcm_test表的统计数据，与delete操作之前并没有变化
39. SELECT COUNT(*) FROM lcm_test;
41. -- 回收表被删除空间
42. alter table lcm_test enable row MOVEMENT;
43. alter table lcm_test shrink SPACE;
45. -- 收缩之后，lcm_test只剩下一个块，全表扫描瞬间完成

[1] oracle高水位线问题 http://blog.chinaunix.net/u2/60332/showart_495441.html