delete 与全表扫描

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 做无用功。我们应该考虑通过那些方式 避免这种情况的出现。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26110315/viewspace-722053/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26110315/viewspace-722053/