Oracle截断表

作者：--    发布时间：2019-11-20

在本教程中，您将学习如何使用oracle truncate table语句更快更有效地从表中删除所有数据(也叫截断表)。

oracle truncate table语句简介

如果要从表中删除所有数据，可以使用不带where子句的delete语句，如下所示：

delete from table_name;

对于有少量行记录的表，delete语句做得很好。 但是，当拥有大量行记录的表时，使用delete语句删除所有数据效率并不高。

oracle引入了truncate table语句，用于删除大表中的所有行。

以下说明了oracle truncate table语句的语法：

truncate table schema_name.table_name
[cascade]
[[ preserve | purge] materialized view log ]]
[[ drop | reuse]] storage ]

默认情况下，要从表中删除所有行，请指定要在truncate table子句中截断的表的名称：

truncate table table_name;

在这种情况下，因为我们没有明确指定模式名称，所以oracle假定从当前的模式中截断表。
如果表通过外键约束与其他表有关系，则需要使用cascade子句：

truncate table table_name
cascade;

在这种情况下，truncate table cascade语句删除table_name表中的所有行，并递归地截断链中的关联表。

请注意，truncate table cascade语句需要使用on delete cascade子句定义的外键约束才能工作。

通过materialized view log子句，可以指定在表上定义的物化视图日志是否在截断表时被保留或清除。 默认情况下，物化视图日志被保留。

storage子句允许选择删除或重新使用由截断行和关联索引(如果有的话)释放的存储。 默认情况下，存储被删除。

请注意，要截断表，它必须在您自己的模式中，或者必须具有drop any table系统权限。

oracle truncate table示例

下面我们来看看使用truncate table语句来删除表的一些例子。

1. oracle truncate table简单的例子

以下语句创建一个名为customers_copy的表，并从示例数据库中的customers表复制数据：

create table customers_copy 
as
select
  *
from
  customers;

要从customers_copy表中删除所有行，请使用以下truncate table语句：

truncate table customers_copy;

2. oracle truncate table cascade示例

首先，创建用来演示的两个表：quotations和quotation_items表：

create table quotations (
    quotation_no numeric generated by default as identity,
    customer_id numeric not null,
    valid_from date not null,
    valid_to date not null,
    primary key(quotation_no)
);

create table quotation_items (
    quotation_no numeric,
    item_no numeric ,
    product_id numeric not null,
    qty numeric not null,
    price numeric(9 , 2 ) not null,
    primary key (quotation_no , item_no),
    constraint fk_quotation foreign key (quotation_no)
        references quotations
        on delete cascade
);

接下来，在这两个表中插入一些行：

insert into quotations(customer_id, valid_from, valid_to)
values(100, date '2017-09-01', date '2017-12-01');

insert into quotation_items(quotation_no, item_no, product_id, qty, price)
values(1,1,1001,10,90.5);

insert into quotation_items(quotation_no, item_no, product_id, qty, price)
values(1,2,1002,20,200.5);

insert into quotation_items(quotation_no, item_no, product_id, qty, price)
values(1,3,1003,30, 150.5);

然后，截断quotatios表：

truncate table quotations;

执行语句失败，oracle返回以下错误：

sql error: ora-02266: unique/primary keys in table referenced by enabled foreign keys

要解决这个问题，可以将cascade子句添加到上面的truncate table语句中：

truncate table quotations cascade;

这个语句不仅从quotations表中删除数据，而且还从quotation_items表中删除数据。最后，验证是否删除了quotations和quotation_items中的数据：

select
  *
from
  quotations;

select
  *
from
  quotation_items;

请注意，如果没有为fk_quotation约束指定on delete cascade，则上面的truncate table cascade语句将失败。

在本教程中，我们学习了如何使用oracle truncate table语句更快更有效地从表中删除所有数据。