Truncate数据表背后的几个参数（上）

最新推荐文章于 2024-05-18 11:19:23 发布
最新推荐文章于 2024-05-18 11:19:23 发布
阅读量357 收藏
点赞数
文章标签： 数据库

 

Truncate语句是Oracle SQL体系中非常有特色的一个。Truncate直接的效果是进行数据表数据的清理，深层次是一种典型的DDL语句。

Oracle中，delete语句是一种标注动作。在执行过程中，数据库会访问每个符合删除条件的数据行进行标注动作，标记为“已删除”。删除的数据范围越大、执行路径越长，执行SQL语句时间也就越长。所以说，delete操作是一个和数据规模成正比的执行过程。

而Truncate操作最多接触的知识点是DDL本质。Truncate操作下，Oracle并不关注每个数据行和数据范围，而是集中修改段头结构、更新核心数据字典上。对于特别巨大的数据表，Truncate操作速度要显著快于delete操作。

在11.2.0.x系列版本中，我们还有一些参数可以用来控制Truncate数据表的行为。具体包括：drop storage、drop all storage和reuse storage，每个选项对应truncate数据表的不同行为。本文集中介绍参数的几个选项。

 

1、环境介绍

 

笔者使用Oracle 11gR2进行测试，版本是11.2.0.4。

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE  11.2.0.4.0    Production

TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

创建专门的非sys用户，注意：这个细节很重要。

 

 

SQL> create user test identified by test;

User created

 

SQL> grant connect, resource to test;

Grant succeeded

 

SQL> grant select_catalog_role to test;

Grant succeeded

 

SQL> grant select any dictionary to test;

Grant succeeded

 

 

登录实验环境，创建数据表。

 

 

SQL> conn test/test@sicsdb

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as test

 

SQL> show user

User is "test"

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

对应数据段和索引段结构如下：

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28808      65536          8

         1          4      28816      65536          8

         2          4      28824      65536          8

（篇幅原因，有省略……）

        26          4      30336    1048576        128

 

27 rows selected

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28936      65536          8

         1          4      28944      65536          8

（篇幅原因，有省略……）

        15          4      30464      65536          8

        16          4      30592    1048576        128

 

17 rows selected

 

 

2、Truncate drop storage行为

 

Truncate数据表默认行为包括了drop storage参数。使用drop storage之后，数据表中所有数据都被清空，数据表和索引段只保留一个分区结构。

 

 

SQL> truncate table t drop storage;

 

Table truncated

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28808      65536          8

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28936      65536          8

 

 

注意：虽然两个段头分区extent的大小和起始段都没有发生变化，依然保持了28808和28936。但是数据字典结构中，认为是一个新的段结构。

 

 

SQL> select object_name, object_id, data_object_id from dba_objects where owner='TEST' and object_name in ('T','IDX_T_ID');

 

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID

--------------- ---------- --------------

IDX_T_ID            123667         123668

T                   123666         123669

 

 

T和IDX_T_ID的object_id和data_object_id不一致了。Data_object_id是内部段结构的编号信息。一旦进行truncate操作，就会认为是一个新段生成。

默认truncate操作下，Oracle会删除所有数据，回收所有段结构后重新分配一个新的extent。内部的段结构上，Oracle认为是在原来段头位置上重新分配的新段。

 

3、Truncate reuse storage行为

 

下面来测试一下reuse storage参数行为。首先需要重建表数据内容和充实段结构。

 

 

SQL> insert into t select * from dba_objects;

99693 rows inserted

 

SQL> commit;

Commit complete

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28808      65536          8

         1          4      28816      65536          8

（篇幅原因，有省略……）

        26          4      30720    1048576        128

 

27 rows selected

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28936      65536          8

         1          4      28840      65536          8

（篇幅原因，有省略……）

        17          4      30208    1048576        128

 

18 rows selected

 

 

 

操作reuse storage。

 

 

SQL> truncate table t reuse storage;

 

Table truncated

 

 

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28808      65536          8

         1          4      28816      65536          8

（篇幅原因，有省略……）

        26          4      30720    1048576        128

 

27 rows selected

 

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';

 

 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS

---------- ---------- ---------- ---------- ----------

         0          4      28936      65536          8

         1          4      28840      65536          8

         2          4      28904      65536          8

（篇幅原因，有省略……）

        17          4      30208    1048576        128

 

18 rows selected

 

 

数据的确删除。

 

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

         0

 

 

Reuse storage情况下，段结构没有回收，数据却被删除了！从段结构情况看，Oracle依然视之为新段，data_object_id发生变化。

 

 

SQL> select object_name, object_id, data_object_id from dba_objects where owner='TEST' and object_name in ('T','IDX_T_ID');

 

OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID

--------------- ---------- --------------

IDX_T_ID            123667         123670

T                   123666         123671

 


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

转载于:http://blog.itpub.net/17203031/viewspace-1480093/

ciqu9915
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Truncate数据表背后的几个参数（下）
ciqu9915的博客
04-03 126
4、Truncate drop all storage行为 在11gR2中，Oracle有一个新特性就是deferred_segment_creation参数的引用。在之前的版本...
oracle truncate 参数,Truncate数据表背后的几个参数（上）
weixin_35778245的博客
04-14 692
Truncate语句是Oracle SQL体系中非常有特色的一个。Truncate直接的效果是进行数据表数据的清理，深层次是一种典型的DDL语句。Oracle中，delete语句是一种标注动作。在执行过程中，数据库会访问每个符合删除条件的数据行进行标注动作，标记为“已删除”。删除的数据范围越大、执行路径越长，执行SQL语句时间也就越长。所以说，delete操作是一个和数据规模成正比的执行过程。而T...
在Oracle中使用TRUNCATE TABLE清空多个表应用实例
最新发布
gongjin28_csdn的博客
05-18 787
在Oracle数据库中，TRUNCATE TABLE 命令是针对单个表的操作，不直接支持在一个语句中清空多个表。但这并不意味着我们无法实现批量清空多个表的需求。这里探讨如何在Oracle环境中高效地对多个表执行 TRUNCATE TABLE，并提供实际的应用场景示例。
第七十七章 SQL命令 TRUNCATE TABLE
yaoxin521123的博客
11-16 1533
文章目录 第七十七章 SQL命令 TRUNCATE TABLE大纲参数描述Fast TruncateFast Truncate 限制Atomicity限制参数参照完整性事务锁Imported SQL代码示例 第七十七章 SQL命令 TRUNCATE TABLE 从表中删除所有数据并重置计数器。 大纲 TRUNCATE TABLE [restriction] tablename 参数 restriction - 可选—以下限制关键字中的一个或多个，用空格隔开:%NOCHECK， %NOLOCK。 tab
TRUNCATE 命令用法
杂记
12-11 11万+
删除一表结构,报错,可能是锁表原因.用TRUNCATE指令后Done!删除表中的所有行，而不记录单个行删除操作。 语法TRUNCATE TABLE name参数name是要截断的表的名称或要删除其全部行的表的名称。注释TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比
Truncate Table 用法
懒人改变世界
07-07 1万+
TRUNCATE TABLE 删除表中的所有行，而不记录单个行删除操作。 语法TRUNCATE TABLE name参数name是要截断的表的名称或要删除其全部行的表的名称。 注释 TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
TRUNCATE 快速删除表中的所有数据
09-11
`TRUNCATE` 是 SQL 中的一个重要命令，用于迅速清空表中的所有数据。与 `DELETE` 语句相比，`TRUNCATE TABLE` 在处理大量数据时具有显著的优势，尤其是在速度和资源消耗方面。 首先，`TRUNCATE TABLE` 的主要作用是...
ORACLE truncate 数据恢复
01-16
在Oracle数据库中，`TRUNCATE TABLE`命令用于删除表中的所有数据，但它与`DELETE`语句有所不同。`TRUNCATE`操作是DDL（Data Definition Language）的一部分，而`DELETE`是DML（Data Manipulation Language）。由于`...
t-sql清空表数据的两种方式示例(truncate and delete)
09-10
在SQL Server中，清空表数据是常见的数据库维护操作，主要使用`TRUNCATE`和`DELETE`两种语句。本文将详细介绍这两种方法，并对比它们的差异和适用场景。 首先，`TRUNCATE TABLE`是一种高效的方式来清空表中的所有...
Oracle恢复truncate删除表的数据.pdf
02-27
【Oracle 恢复 Truncate 删除表的数据】 在Oracle数据库中，`TRUNCATE TABLE`命令用于删除表中的所有数据，但保留表结构。与`DELETE`语句不同，`TRUNCATE`不记录任何删除操作，因此无法通过常规的事务回滚来恢复...
datax参数设置_DataX使用
weixin_29109553的博客
01-26 2398
解决问题？DataX简介？DataX使用？DataX配置文件？1. DataX简介？DataX 是阿里巴巴集团内被广泛使用的离线数据同步工具/平台，实现包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS 等各种异构数据源之间高效的数据同步功能。使用DataX能实现什么...
truncate()函数
weixin_30633507的博客
05-11 4713
1 truncate()方法用于截断文件，如果指定了可选参数 size，则表示截断文件为 size 个字符，截断之后 size 后面的所有字符被删除。 参考： https://www.runoob.com/python/file-truncate.html 2 可以筛选时间 1 import pandas as pd 2 import datetime 3 df = pd.read_...
truncate函数
u012135077的专栏
11-03 9454
1.TRUNC(for dates) 　　TRUNC函数为指定元素而截去的日期值。 　　其具体的语法格式如下： 　　TRUNC（date[,fmt]） 　　其中： 　　date 一个日期值 　　fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去 　　下面是该函数的使用情况： 　　TRUNC(TO_DATE(‘24-Nov-1999 08:00 pm’),’dd-mon-yyy
设置文件的属性-chmod/chown/truncate/utime/umask
b711183612的专栏
10-20 407
设置文件属性 1.chmod/fchmod 用于修改文件的存取权限 2.chown/fchown/lchown 用于修改文件的用户id和组id int chown(const char *path, uid_t owner,gid_t group) int fchown(int fd, uid_t owner, gid_t group) int lchown(const char *
SQL中TRUNCATE与DELETE区别
方法属性类，一个都不会
05-18 559
删除和舍弃 标准 SQL 中用来从表中删除数据的只有 DELETE 语句。但是，很多数据库产 品中还存在另外一种被称为 TRUNCATE 的语句。这些产品主要包括 Oracle、SQL Server、PostgreSQL、MySQL 和 DB2。 TRUNCATE 是舍弃的意思，具体的使用方法如下所示。 语法4-A　只能删除表中全部数据的TRUNCATE语句 TRUNCATE <表名>; 与 DELETE 不同的是，TRUNCATE 只能删除表中的全部数据，...
Truncate用法详解
热门推荐
kunjian的博客
04-03 20万+
前言： 当我们想要清空某张表时，往往会使用truncate语句。大多时候我们只关心能否满足需求，而不去想这类语句的使用场景及注意事项。本篇文章主要介绍truncate语句的使用方法及注意事项。 1.truncate使用语法 truncate的作用是清空表或者说是截断表，只能作用于表。truncate的语法很简单，后面直接跟表名即可，例如： truncate table tbl_name ...
TRUNCATE TABLE -- SQL Server 不记录日志的删除，对非常大的表非常有用。同时计数值复元
11-29 3158
TRUNCATE TABLE删除表中的所有行，而不记录单个行删除操作。 语法TRUNCATE TABLE name参数name是要截断的表的名称或要删除其全部行的表的名称。注释TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
表的storage (MINEXTENTS 属性对truncate后表大小的影响
还不算晕的专栏
02-23 1169
TRUNCATE表后对表大小进行查询，表的空间没有释放完毕？ 对此进行实验测试，结果如下：(测试环境LINUX+ORACLE11.2.0.3) 创建表时语句指定了storage (MINEXTENTS 5);时，TRUNCATE后还有5个EXTENT； 创建表时语句不指定storage (MINEXTENTS参数，即使用默认值时；TRUNCATE后只有1个EXTENT； ---------
TRUNCATE TABLE 清空表分区数据
11-24
是的，TRUNCATE TABLE命令可以用于清空表或表分区中的数据。在Oracle数据库中，可以使用以下语句清空表或表分区中的数据： ```sql TRUNCATE TABLE table_name; TRUNCATE TABLE partition_name; ``` 其中，table_name...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值