ITPub博客

首页 > Linux操作系统 > Linux操作系统 > delete 与全表扫描

delete 与全表扫描

原创 Linux操作系统 作者:pingley 时间:2012-04-23 17:11:20 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 启用plustrace 角色
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    715620