今天同事问了一个问题,说是一个千万级的表,删除了500W的数据,怎么查询性能没有明显的提高。这个问题其实跟高水位HWM这个概念有关,当创建一个表的时候,oracle就会为这个对象分配一个段,段下面有多个extent,一个extent包含多个连续的块,数据的最小存储单位是块,这张表的数据持续增多的时候,那么使用的块会越来越多,这个HWM就表示某个块以下所有的块全部存过数据了。举个例子,比如一个水槽,水上升的时候在最高处会有一个标记,但是当水退掉后,这个标记不会下降!、
简单的了解了上面的概念,我们就能解释上面的问题了,为什么删除了大部分数据,查询性能没有明显的提升,因为数据是删除了,但是HWM没有下降,那么对表的扫描还是扫描了HWM以下所有的块,性能没有明显的提升,下面我们简单的演示一下:
SQL> create table test(a int);
简单的了解了上面的概念,我们就能解释上面的问题了,为什么删除了大部分数据,查询性能没有明显的提升,因为数据是删除了,但是HWM没有下降,那么对表的扫描还是扫描了HWM以下所有的块,性能没有明显的提升,下面我们简单的演示一下:
SQL> create table test(a int);
Table created.
SQL> set timing on
#插入1000W测试数据(这个插数据的过程还可以优化,这地方就简单的插下了)
SQL> begin
2 for i in 1..10000000 loop
3 insert into test values(i);
4 if mod(i,10000)=0 then
5 commit;
6 end if;
7 end loop;
8 end;
9 /
#插入1000W测试数据(这个插数据的过程还可以优化,这地方就简单的插下了)
SQL> begin
2 for i in 1..10000000 loop
3 insert into test values(i);
4 if mod(i,10000)=0 then
5 commit;
6 end if;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:08:22.19
#查询一下这个表
SQL> select count(*) from test;
#查询一下这个表
SQL> select count(*) from test;
COUNT(*)
----------
10000000
----------
10000000
Elapsed: 00:00:05.01
#删除一半的数据
SQL> delete from test where a<=5000000;
#删除一半的数据
SQL> delete from test where a<=5000000;
5000000 rows deleted.
Elapsed: 00:02:22.33
#再次查询这个表
SQL> select count(*) from test;
#再次查询这个表
SQL> select count(*) from test;
COUNT(*)
----------
5000000
----------
5000000
Elapsed: 00:00:04.48
看这个结果,按照正常的理解,少了一半的数据,性能应该要提升1倍吧,但是测试结果,两个查询时间几乎一样,没有大的差别,这就验证了上面说的HWM问题。那么有没有办法可以解决这个问题呢,回收了这个高水位不就可以了嘛,那么有没有办法来回收高水位呢,10g以后回收比较方便,直接用shrink就行了。如下:
SQL> alter table test enable row movement;
看这个结果,按照正常的理解,少了一半的数据,性能应该要提升1倍吧,但是测试结果,两个查询时间几乎一样,没有大的差别,这就验证了上面说的HWM问题。那么有没有办法可以解决这个问题呢,回收了这个高水位不就可以了嘛,那么有没有办法来回收高水位呢,10g以后回收比较方便,直接用shrink就行了。如下:
SQL> alter table test enable row movement;
Table altered.
Elapsed: 00:00:00.14
SQL> alter table test shrink space;
SQL> alter table test shrink space;
Table altered.
Elapsed: 00:03:42.36
#再次查询这个表
SQL> select count(*) from test;
SQL> select count(*) from test;
COUNT(*)
----------
5000000
----------
5000000
Elapsed: 00:00:00.52
可以看到这里的查询速度得到了明显的提升,这个是在10g后才可以使用的方法,我们也可以通过move重建,达到一样的效果。这里就不试验了,简单的给下sql:
alter table test move;后面可以加表空间,表示move到一个新的表空间,不加表空间就表示还在当前的表空间!
可以看到这里的查询速度得到了明显的提升,这个是在10g后才可以使用的方法,我们也可以通过move重建,达到一样的效果。这里就不试验了,简单的给下sql:
alter table test move;后面可以加表空间,表示move到一个新的表空间,不加表空间就表示还在当前的表空间!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26463985/viewspace-713850/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26463985/viewspace-713850/