Delete删除表数据时对性能的影响分析

本文通过实际测试对比了使用delete和truncate操作对数据库性能的影响。delete会产生大量redo并增加逻辑读,而truncate虽能降低高水位但仍留有一定的空间浪费。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

上一篇博客中提到,当我们用delete删除数据时,因为无法降低高水位,对表做全表扫描时会带来数据库性能问题,而truncate可以大大改善这一状况,下面来看测试

SQL> conn zlm/zlm
Connected.

--创建表之前先记录下剩余表空间大小
SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
ZLM                                     6        128   51380224       6272            6

--创建一个100W行的大表
SQL> create table test1(int number);

Table created.

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------------ ---------- ---------- ------------ ----------- ------------------
TEST1                                   0          0            0           0 27-SEP-14

注意,创建完表结构而未插入数据时,表的高水位是0,并没有为该表分配任何数据块

SQL> declare
  2  i number := 0;
  3  begin
  4  for i in 1 .. 1000000 loop
  5  insert into test1 values (i);
  6  end loop;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000

--查看高水位状况
SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
----------- ------------ ---------- ---------- ----------
          6          130   13631488       1664         28

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'TEST%';

  BLOCK_ID  EXTENT_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
       128          0      65536          8
       136          1      65536          8
       144          2      65536          8
       152          3      65536          8
       160          4      65536          8
       168          5      65536          8
       176          6      65536          8
       184          7      65536          8
       192          8      65536          8
       200          9      65536          8
       208         10      65536          8
       216         11      65536          8
       224         12      65536          8
       232         13      65536          8
       240         14      65536          8
       248         15      65536          8
       256         16    1048576        128
       384         17    1048576        128
       512         18    1048576        128
       640         19    1048576        128
       768         20    1048576        128
       896         21    1048576        128
      1024         22    1048576        128
      1152         23    1048576        128
      1280         24    1048576        128
      1408         25    1048576        128
      1536         26    1048576        128
      1664         27    1048576        128

SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
ZLM                                     6       1792   37748736       4608            6

SQL> set autot trace
SQL> select count(*) from test1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
       1599  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> delete from test1;

1000000 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 2642947686

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | DELETE STATEMENT   |       |     1 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | TEST1 |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------


Statistics
----------------------------------------------------------
        814  recursive calls
    1038983  db block gets
       1953  consistent gets
          8  physical reads
  245334988  redo size
        847  bytes sent via SQL*Net to client
        769  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

用delete删除100W行数据的大表TEST1时,产生了大量的redo(2亿多),另外还有很多一致性读(1953),读取了100多W个数据块,814次递归调用,可以看到,用delete删除表记录,对数据库的性能消耗是很大的,尤其是当delete大量行的时候

SQL> set autot off
SQL> select count(*) from test1;

  COUNT(*)
----------
         0

SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
----------- ------------ ---------- ---------- ----------
          6          130   13631488       1664         28

--用delete删除数据后查看执行计划
SQL> set autot trace
SQL> select count(*) from test1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
       1599  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

此时虽然表中已经没有一条记录了,但是由于TEST1表并没有索引,走的是全表扫描,全表扫描就是要从hearder_block的值一致扫描到blocks得值,即从block 130一致到block 1664,可以看到,即使去访问一个空表,也要消耗1599次逻辑读,这就是高水位没有下降的缘故,之前说过,delete操作并不会降低表的高水位,带来的副作用就是访问该表时带来的性能下降(产生大量逻辑读)

--truncate表后再次查看统计信息
SQL> truncate table test1;

Table truncated.

SQL> set autot trace
SQL> select count(*) from test1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1 |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          1  db block gets
          7  consistent gets
          0  physical reads
         96  redo size
        525  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

此时再去全表扫描TEST1表,只有7次一致性读了,大大降低了需要扫描的数据库块,只有1个,

--查看此时TEST1表的表信息
SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------------ ---------- ---------- ------------ ----------- ------------------
TEST1                                   0          0            0           0 27-SEP-14

--似乎不太准确,重新收集一下统计信息
SQL> analyze table test1 compute statistics;

Table analyzed.

--再次查看
SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------------ ---------- ---------- ------------ ----------- ------------------
TEST1                                   0          0                      0 27-SEP-14

可以看到,truncate已经把高水位降低到8了,而且是8个空块(EMPTY_BLOCKS),表示高水位以下未使用的空间,即第一个EXTENT为TEST1表分配的BLOCK空间,并且是最低值了,这与我们刚才是创建的只有表结构的空表不一样,同样是没有行数据的表,刚才创建表结构时的高水位为0。因此可以这么说,truncate降低高水位的作用也是有限的,还是剩余了1个extent的blocks的高水位,并没有完全消除。如果默认1个extent的block要大于8,那么高水位也要超过8

SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
----------- ------------ ---------- ---------- ----------
          6          130      65536          8          1

dba_segment视图也可以反映这一高水位的情况,如果对表做全表扫描,就是从130开始,扫描8个数据块,而并非之前的从130扫描到1664了

--查看剩余表空间容量
SQL> select * from dba_free_space where tablespace_name='ZLM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
ZLM                                     6        136   51314688       6264            6

truncate表数据后,ZLM剩余表空间又变大了,但是要注意,又多了8个block的消耗,建表之前查看的BLOCK_ID值是128

总结:

delete删除数据会读取大量的数据块,并产生大量的redo,对数据库产生性能影响,尤其是对大表操作时,删除大量的行数据时,而truncate虽然可以有效降低高水位,但其也有一定的局限性:
1. 并不能完全消除空表的高水位,仍然会有一定的空间浪费(8K)
2. 无法用于非清空全表数据的删除的场景


<think>好的,用户问的是OracleDELETE和DROP操作的区别,特别是关于删除还是数据的问题。首先,我需要明确这两个命令的基本定义和用途。 DELETE是DML(数据操作语言)命令,用于删除中的部分或全部数据,但保留结构。也就是说,执行DELETE后,本身还存在,只是数据被清空了。这候用户可能需要考虑事务控制,比如使用COMMIT或ROLLBACK来确认或撤销操作。另外,DELETE操作会记录在日志中,因此在数据删除可能会影响性能,并且会占用undo空间。需要提醒用户注意这一点。 而DROP是DDL(数据定义语言)命令,用于删除整个,包括结构、数据、索引、触发器等所有相关对象。执行DROP后,就不存在了,需要重建才能再次使用。这个操作是自动提交的,无法回滚,所以风险更,用户需要谨慎使用。特别是生产环境中,误操作可能导致严重问题。 接下来,用户可能想知道具体的使用场景。比如,当只需要清除数据但保留结构,使用DELETE;当确定不再需要整个,才使用DROP。此外,TRUNCATE也是一个相关的命令,它属于DDL,会快速清空数据并释放空间,但同样保留结构,这可能是一个中间选项,可以简要提及。 然后,考虑到用户可能对这两个命令的具体语法不太清楚,需要给出示例。比如DELETE FROM table WHERE condition; 和DROP TABLE table;。同,强调WHERE条件的重要性,避免误删数据。 还要注意权限问题。执行DELETE需要删除权限,而DROP需要更高的DDL权限,这可能涉及系统权限管理,用户可能需要检查自己的权限设置。 另外,数据恢复方面。DELETE操作可以通过回滚或备份恢复,而DROP之后需要从备份中恢复或使用闪回功能(如果可用)。这部分信息对用户来说也很重要,尤其是在误操作后的应对措施。 最后,用户可能需要了解性能影响DELETE逐行删除,产生较多日志,速度较慢;而DROP直接删除数据,速度快,但不可逆。TRUNCATE则更快,因为它不记录每一行的删除动作。 总结,需要分点明确两者的区别,并给出使用建议,帮助用户根据实际需求选择合适的操作。同,生成的相关问题应该覆盖用户可能进一步关心的内容,比如TRUNCATE的区别、恢复方法以及性能比较等。</think>### OracleDELETE与DROP操作的区别 在Oracle数据库中,`DELETE`和`DROP`是两个完全不同的操作,**主要区别在于操作对象和影响范围**: 1. **DELETE操作** - **作用对象**:中的数据(行)。 - **功能**:删除中符合条件的记录(通过`WHERE`子句指定),若未指定条件则删除全部数据。 - **结构保留**:仅删除数据的结构(列定义、索引、约束等)仍然存在[^1]。 - **事务控制**:属于DML(数据操作语言)命令,需通过`COMMIT`提交或`ROLLBACK`回滚。 - **日志与性能**:逐行删除,生成重做日志(Redo Log)和撤销日志(Undo Log),可能导致性能问题。 示例: ```sql DELETE FROM employees WHERE department_id = 10; ``` 2. **DROP操作** - **作用对象**:整个或其他数据库对象(如索引、视图)。 - **功能**:直接删除及其所有数据、结构、索引、触发器等关联对象。 - **不可逆性**:属于DDL(数据定义语言)命令,执行后立即生效且无法回滚[^2]。 - **空间释放**:释放占用的存储空间,需重新创建才能使用。 示例: ```sql DROP TABLE employees; ``` #### 对比总结 | 操作 | 对象 | 是否保留结构 | 事务控制 | 恢复难度 | |---------|------------|--------------|----------|----------| | DELETE | 数据行 | 是 | 支持 | 易 | | DROP | 及其数据 | 否 | 不支持 | 难 | #### 使用场景建议 - **需删除部分数据**:使用`DELETE`并添加`WHERE`条件。 - **需快速清空**:考虑`TRUNCATE TABLE`(DDL命令,比`DELETE`更快且不记录日志)[^3]。 - **需彻底删除**:使用`DROP TABLE`,但需提前备份。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值