Several parameters behind the Truncate data table

The Truncate statement is a distinctive statement in the OracleSQL system. The direct effect of Truncate is to clean up data in the data table. It is a typical DDL statement at a deeper level.

The Truncate statement is a distinctive one in the Oracle SQL System. The direct effect of Truncate is to clean up data in the data table. It is a typical DDL statement at a deeper level.

The Truncate statement is a distinctive one in the Oracle SQL System. The direct effect of Truncate is to clean up data in the data table. It is a typical DDL statement at a deeper level.

In Oracle, the delete statement is a annotation action. During the execution, the database accesses each row that meets the deletion conditions to mark the operation as "deleted ". The larger the scope of the deleted data and the longer the execution path, the longer the SQL statement execution time. Therefore, the delete operation is an execution process proportional to the data size.

The most common knowledge of Truncate operations is the essence of DDL. In the Truncate operation, Oracle does not focus on each data row and data range. Instead, it modifies the segment header structure and updates the core data dictionary. For extremely large data tables, the Truncate operation speed is significantly faster than the delete operation.

In the 11.2.0.x series, we also have some parameters that can be used to control the behavior of the Truncate data table. Specific actions include drop storage, drop all storage, and reuse storage. Each option corresponds to different actions of the truncate data table. This article describes several parameters.

1. Environment Introduction

I used Oracle 11gR2 for testing. The version is 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

Note: This is very important to create special non-sys users.

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

Log on to the lab environment and create a data table.

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

The structure of the corresponding data segment and index segment is as follows:

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

(Space reasons, omitted ......)

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

(Space reasons, omitted ......)

15 4 30464 65536 8

16 4 30592 1048576 128

17 rows selected

2. Truncate drop storage behavior

The default actions of the Truncate data table include the drop storage parameter. After drop storage is used, all data in the data table is cleared, and only one partition structure is retained for the data table and index segment.

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

Note: although the extent size and start segment of the two segment headers have not changed, they are still 28808 and 28936. However, the data dictionary structure is considered as a new segment structure.

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

The object_id and data_object_id of T and IDX_T_ID are inconsistent. Data_object_id is the ID of the internal segment structure. Once the truncate operation is performed, a new segment is generated.

By default, Oracle deletes all data in the truncate operation, recycles the structure of all segments, and allocates a new extent. In terms of the Internal Segment Structure, Oracle considers it as a new segment that is re-allocated at the original segment header position.

3. Truncate reuse storage behavior

Next we will test the reuse storage parameter behavior. First, you need to reconstruct the table data content and enrich the segment structure.

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

(Space reasons, omitted ......)

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

(Space reasons, omitted ......)

17 4 30208 1048576 128

18 rows selected

Operate 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

(Space reasons, omitted ......)

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

(Space reasons, omitted ......)

17 4 30208 1048576 128

18 rows selected

The data is indeed deleted.

SQL> select count (*) from t;

COUNT (*)

----------

0

In the case of Reuse storage, the segment structure is not recycled, but the data is deleted! From the perspective of segment structure, Oracle still regards it as a new segment, and data_object_id changes.

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

For more details, please continue to read the highlights on the next page: