Truncate数据表背后的几个参数

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 ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for 64-bit Windows: Version 11.2.0.4.0 - ProductionNLSRTL 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@sicsdbConnected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as test SQL> show userUser 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 selected2、Truncate drop storage行为Truncate数据表默认行为包括了drop storage参数。使用drop storage之后，数据表中所有数据都被清空，数据表和索引段只保留一个分区结构。SQL> truncate table t drop storage;Table truncatedSQL> 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 123668T 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 123670T 123666 123671更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2015-04/115921p2.htm

Oracle 中函数如何返回结果集Oracle 10g,11g中EM的安装配置过程相关资讯 Truncate

存在外键关联的主表truncate如何做（06/09/2015 16:22:57）

用Delete与Truncate清除表数据对高（09/28/2014 11:38:43）

本文评论查看全部评论（0）