delete 与全表扫描
先创建一张测试用的表。并填充数据。把表造的大小才有比较的效果。
SQL> create table testing01 (id number(8),col2 varchar2(30));
Table created.
SQL> begin
2 for i in 1..1000000
3 loop
4 insert into testing01 values(i,'This is a testing record');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
通过下面的查询对刚才创建的表有一个基本的认识。
SQL> select segment_name,blocks,bytes/1024 as "Size[KB]"
2 from user_segments
3 where segment_name = 'TESTING01';
SEGMENT_NAME BLOCKS Size[KB]
--------------- ---------- ----------
TESTING01 4992 39936
计算HWM（high water mark），所谓的高水位线用来界定segment 中已经使用过的block 与没有使用的block.已经使用的block 中不一定实际的存储有数据。
SQL> select blocks from user_segments
2 where segment_name = 'TESTING01';
BLOCKS
----------
4992
SQL> analyze table testing01 estimate statistics;
Table analyzed.
SQL> select empty_blocks
2 from user_tables
3 where table_name = 'TESTING01';
EMPTY_BLOCKS
------------
86
hwm =total_blocks – unused_blocks – 1.
HWM=4905
在segment testing01。使用使用过的block 数量是4905.执行如下的查询查看执行计划。
SQL> set autotrace traceonly
SQL> select * from testing01;
1000000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3696461997
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1013K| 27M| 1512 (13)| 00:00:19 |
| 1 | TABLE ACCESS FULL| TESTING01 | 1013K| 27M| 1512 (13)| 00:00:19 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
74301 consistent gets
315 physical reads
201404 redo size
43646886 bytes sent via SQL*Net to client
733745 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
把表中的记录全部delete ，在确定该表对应的segment 的HWM.
SQL> delete from testing01;
1000000 rows deleted.
SQL> select blocks from user_segments
2 where segment_name = 'TESTING01';
BLOCKS
----------
4992
SQL> select blocks from user_segments
2 where segment_name = 'TESTING01';
BLOCKS
----------
4992
SQL> select empty_blocks
2 from user_tables
3 where table_name = 'TESTING01';
EMPTY_BLOCKS
------------
86
通过查询和使用公式hwm =total_blocks – unused_blocks – 1.
我们可以知道hwm 没有下降，hwm 之所以没有下降的原因是保留
这部分空间为了以后的update,insert 的需要。在这种情况下进行
全部扫描会是怎样的情形呢？
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from testing01;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3696461997
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1013K| 27M| 1512 (13)| 00:00:19 |
| 1 | TABLE ACCESS FULL| TESTING01 | 1013K| 27M| 1512 (13)| 00:00:19 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
711 recursive calls
0 db block gets
4964 consistent gets
4869 physical reads
0 redo size
340 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autotrace off
我们知道表中没有数据。通过执行计划我们可以知道这条sql 语句在执行的时候还是进行了。全表扫描读取了很多的数据块。因为oracle 在执行全表扫描的时候会读取对象对应的segment中第一个block 到HWM之间的blocks。因为delete 不会降低HWM ,所以sql 执行的时候如果
要进行全表扫描，那么oracle 很”无辜“需要多做很多额外的IO操作。为了不让oracle 无辜的读取那些在HWM下没有数据的block，我们需要尝试降低HWM。可选的方式如下。
1、truncate 一张表，降低HWM 释放空间。如果表空中的数据都不再需要可以选用这种方式。
当时如果我想要删除某些行这种方式就不行了。在很多时候可以结合表分区技术。
2、使用shrink 的方式，但是会导致索引的无效对index 需要rebuilt.步骤比较多。
3、把表drop 了重建。这可能是最烂的方式，因为要考虑到表之间复杂的参照关系，数据的填充花费的时间和系统资源。
对testing01 进行shrink。我没有创建索引所以不需要对索引进行rebuilt.
SQL> alter table testing01 move;
Table altered.
SQL> select blocks from user_segments
2 where segment_name = 'TESTING01';
BLOCKS
----------
8
SQL> analyze table testing01 estimate statistics;
Table analyzed.
SQL> select empty_blocks
2 from user_tables
3 where table_name = 'TESTING01';
EMPTY_BLOCKS
------------
8
乖乖，现在HWM线下来了回到了表最初创建的时候的样子指向segment 中第一个block的左边。
今日心得
有时候进行全表扫描是好事，或者说是不可避免的。但是在上面这种情况（或者表中经常进行大量delete 的操作）下就糟糕了，因为oracle在执行sql 语句的时候很无辜需要读取高水位线以下的很多空白的block 做无用功。我们应该考虑通过那些方式避免这种情况的出现。