当前位置：文江博客话题详情

删除/截断和回滚段

发布于 2024-08-29 16:50:17 字数 99 浏览 9 评论 0原文

我知道 drop 删除数据以及表结构，而 truncate 保留表结构。

删除/截断是否写入回滚段？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倥絔 2024-09-05 16:50:17

每个 DDL 语句都是一个离散事务。这是因为数据库需要管理其元数据（Oracle 中的数据字典）。基本上它必须始终正确且有效，因此对元数据的更改无法回滚。因此，在每个 DDL 语句之前和之后都会发出隐式commit。这适用于大多数（可能是所有）RDBMS 产品。

TRUNCATE TABLE和DROP TABLE都是DDL语句，都是DDL，所以没有回滚。如果我们有最新版本 Oracle 的企业版许可证，我们可以使用 FLASHBACK TABLE 将表恢复到以前的状态，包括 BEFORE DROP。

编辑

以下是 DELETE 和 TRUNCATE TABLE 之间的区别。示例日期是这个大表：

SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
     15449    2340320

SQL>

首先删除......

SQL> delete from big_table
  2  /

2340320 rows deleted.

Elapsed: 00:01:20.37
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.20
SQL>
SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
     15449          0

Elapsed: 00:00:00.11
SQL>

然后现在截断......

SQL> truncate table big_table reuse storage
  2  /

Table truncated.

Elapsed: 00:00:08.31
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL>
SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
         0          0

Elapsed: 00:00:00.00
SQL>

之间的差异是显而易见的。 TRUNCATE 的速度要快得多。它还将表中的块数归零。请注意，截断后统计数据的收集速度也会更快。这是因为 TRUNCATE 语句重置了高水位线（即零块），因此作业知道所有分配的块均未使用。

Each single DDL statement is a discrete transaction. This is because databases need manage their metadata (the data dictionary in Oracle). Basically it has to correct and valid all the time, so changes to the metadata cannot be rolled back. Consequently an implicit commit is issued before and after each DDL statement. This is applies to most, probably all, RDBMS products.

Both TRUNCATE TABLE and DROP TABLE are DDL statements are DDL, so there is no rollback. If we have an Enterprise Edition license for a recent version of Oracle we can use FLASHBACK TABLE to recover the table to a previous state, including BEFORE DROP.

edit

Here are the differences between DELETE and TRUNCATE TABLE. The example date is this big table:

SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
     15449    2340320

SQL>

Deletion first....

SQL> delete from big_table
  2  /

2340320 rows deleted.

Elapsed: 00:01:20.37
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.20
SQL>
SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
     15449          0

Elapsed: 00:00:00.11
SQL>

And now the truncate ...

SQL> truncate table big_table reuse storage
  2  /

Table truncated.

Elapsed: 00:00:08.31
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TABLE')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL>
SQL> select blocks, num_rows
  2  from user_tables
  3  where table_name = 'BIG_TABLE'
  4  /

    BLOCKS   NUM_ROWS
---------- ----------
         0          0

Elapsed: 00:00:00.00
SQL>

The differences between are apparent. The TRUNCATE is much, much faster. Also it has zeroised the number of blocks in the table. Notice that even the gathering of statistics is faster after the truncation. This is because the TRUNCATE statement resets the high water mark, (i.e. the zero blocks), so the job knows all the assigned blocks are unused.

回复收藏 0 原文

舞袖。长 2024-09-05 16:50:17

在 Oracle 中，答案是否定的，因为回滚段仅用于 DML。

截断是 DDL。

回复收藏 0 原文

浊酒尽余欢 2024-09-05 16:50:17

删除和截断都会写入回滚段。
您无法自行执行回滚，因为 Oracle 在自动启动和提交的单独事务中执行每个 DDL 语句。

它的工作原理如下：

begin
    COMMIT; -- any outstanding work
    begin
       DDL statement;
       COMMIT; -- the DDL statement
    exception
       when others then
            ROLLBACK;  -- any work done by the DDL
            RAISE;     -- reraise the exception back to the client
    end;
end;

如果系统在 DDL 语句中间崩溃，Oracle 将能够回滚中断的操作。

信息来源：文章 <强>问汤姆。

Both drop and truncate write to rollback segment.
You just cannot do rollback on your own because Oracle executes each DDL statement in separate transaction that is automatically started and commited.

It works like this:

begin
    COMMIT; -- any outstanding work
    begin
       DDL statement;
       COMMIT; -- the DDL statement
    exception
       when others then
            ROLLBACK;  -- any work done by the DDL
            RAISE;     -- reraise the exception back to the client
    end;
end;

In case of system crash in middle of DDL statement Oracle will be able to rollback interrupted operation.

Source of information: article on Ask Tom.

回复收藏 0 原文