《再談 truncate table》

2012-10-15

摘要:《再談 truncate table》

今天的課題延續上次的《Truncate table one or million rows》

比較一下 truncate table drop storage 、 reuse storage

相同之處:

將 HWM (High water mark) 回到 table minextent 的位置

------------------------------------------------------------------------
SQL> create table tingtest as select * from dba_objects;

已建立表格.

SQL> select object_id ,data_object_id from dba_objects where object_name='TINGTE
ST';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    195881         195881

SQL> truncate table tingtest;

表格被截斷.

SQL> select object_id ,data_object_id from dba_objects where object_name='TINGTE
ST';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    195881         195882

SQL> drop table tingtest;

已刪除表格.

SQL> create table tingtest as select * from dba_objects;

已建立表格.

SQL> select object_id ,data_object_id from dba_objects where object_name='TINGTE
ST';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    195883         195883

SQL> truncate table tingtest reuse storage;

表格被截斷.

SQL> select object_id ,data_object_id from dba_objects where object_name='TINGTE
ST';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    195883         195884

SQL>

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

不同之處:

drop storage: 原本的 segment 釋放給其它需要的 Table、Index 使用。

reuse storage: 保留原有的 segment，僅提供原本的 Table 使用。

下面作個實驗比較一下：

SQL> drop table tingtest;

已刪除表格.

SQL> col table_name format a10;
SQL> col num_rows format 99999999;
SQL> col blocks format 99999999;
SQL> col empty_blocks format 99999999;
SQL> col segment_name format a10;
SQL> col segment_type format a10;
SQL>
SQL> create table tingtest as select * from dba_objects;

已建立表格.

SQL> insert into tingtest select * from tingtest;

已建立 44271 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 88542 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 177084 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 354168 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 708336 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 1416672 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 2833344 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 5666688 個資料列.

SQL> commit;

確認完成.

SQL>
SQL> select count(*) from tingtest;

  COUNT(*)
----------
  11333376

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'TINGTEST');

PL/SQL 程序順利完成.

SQL>
SQL> SELECT table_name,num_rows,blocks,empty_blocks from user_tables
  2  WHERE table_name='TINGTEST';

TABLE_NAME  NUM_ROWS    BLOCKS EMPTY_BLOCKS
---------- --------- --------- ------------
TINGTEST    11333376    153220            0

SQL>
SQL> SELECT segment_name,segment_type,blocks from dba_segments
  2  WHERE segment_name='TINGTEST';

SEGMENT_NA SEGMENT_TY    BLOCKS
---------- ---------- ---------
TINGTEST   TABLE         155648


SQL> set timing on;

-- drop storage is default value
SQL> truncate table tingtest drop storage;

表格被截斷.

目前歷時: 00:00:00.01
SQL>
SQL> set timing off;
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'TINGTEST');

PL/SQL 程序順利完成.

SQL>
SQL> SELECT table_name,num_rows,blocks,empty_blocks from user_tables
  2  WHERE table_name='TINGTEST';

TABLE_NAME  NUM_ROWS    BLOCKS EMPTY_BLOCKS
---------- --------- --------- ------------
TINGTEST           0         0            0

SQL>
SQL> SELECT segment_name,segment_type,blocks from dba_segments
  2  WHERE segment_name='TINGTEST';

SEGMENT_NA SEGMENT_TY    BLOCKS
---------- ---------- ---------
TINGTEST   TABLE              8

SQL>
SQL> drop table tingtest;

已刪除表格.

SQL>
SQL> create table tingtest as select * from dba_objects;

已建立表格.

SQL> insert into tingtest select * from tingtest;

已建立 44271 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 88542 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 177084 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 354168 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 708336 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 1416672 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 2833344 個資料列.

SQL> insert into tingtest select * from tingtest;

已建立 5666688 個資料列.

SQL> commit;

確認完成.

SQL>
SQL> select count(*) from tingtest;

  COUNT(*)
----------
  11333376

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'TINGTEST');

PL/SQL 程序順利完成.

SQL>
SQL> SELECT table_name,num_rows,blocks,empty_blocks from user_tables
  2  WHERE table_name='TINGTEST';

TABLE_NAME  NUM_ROWS    BLOCKS EMPTY_BLOCKS
---------- --------- --------- ------------
TINGTEST    11333376    153220            0

SQL>
SQL> SELECT segment_name,segment_type,blocks from dba_segments
  2  WHERE segment_name='TINGTEST';

SEGMENT_NA SEGMENT_TY    BLOCKS
---------- ---------- ---------
TINGTEST   TABLE         155648

SQL>
SQL> set timing on;
SQL>
SQL> truncate table tingtest reuse storage;

表格被截斷.

目前歷時: 00:00:00.02
SQL>
SQL> set timing off;
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'TINGTEST');

PL/SQL 程序順利完成.

SQL>
SQL> SELECT table_name,num_rows,blocks,empty_blocks from user_tables
  2  WHERE table_name='TINGTEST';

TABLE_NAME  NUM_ROWS    BLOCKS EMPTY_BLOCKS
---------- --------- --------- ------------
TINGTEST           0         0            0

SQL>
SQL> SELECT segment_name,segment_type,blocks from dba_segments
  2  WHERE segment_name='TINGTEST';

SEGMENT_NA SEGMENT_TY    BLOCKS
---------- ---------- ---------
TINGTEST   TABLE         155648

SQL>
SQL> drop table tingtest;

已刪除表格.

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

結論:

1. truncate table 確實是更改 HWM 位置，無論是 drop storage、reuse storage，差別只在原有 segment 是否釋放給其它物件使用。

2. truncate table 不會觸發 trigger、不會有 redo log。

3. truncate table 只會異動 dictionary，騙過 database 資料已被刪除。

回首頁