[20131017]11G下truncate的新特性.txt

[20131017]11G下truncate的新特性.txt



11.2.0.2中出現truncate的新特性，截斷表目前有了新選項：即drop all storage。


測試看看：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t as select rownum id ,'test' name from dual connect by level <=2e4 ;
Table created.

SCOTT@test> create index i_t_id on t(id);
Index created.

SCOTT@test> exec dbms_stats.gather_table_stats('','T',cascade=>TRUE);
PL/SQL procedure successfully completed.

SCOTT@test> analyze index i_t_id validate structure;
Index analyzed.

SCOTT@test> @i

    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS

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

         2         56 I_T_ID          20000         44      309700       7996         43          1         496       8028           0               0         20000

MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

                1      359852     310196         87            1                    3          0            0              0                0

SCOTT@test> select segment_name,blocks from dba_segments where segment_name in ('T','I_T_ID') and wner=user;

SEGMENT_NAME             BLOCKS

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

T                            48

I_T_ID                       56

SCOTT@test> truncate table t drop all storage;
Table truncated.

SCOTT@test> select segment_name,blocks from dba_segments where segment_name in ('T','I_T_ID') and wner=user;
no rows selected

--以上可以看到在普通使用者模式下對錶truncate drop all storage後，該表相關的segment均被事實上的drop了。