臨時表的APPEND方式插入

利用APPEND方式插入臨時表，進一步減少臨時表的訪問代價。

臨時表的undo生成：http://yangtingkun.itpub.net/post/468/508719

 

 

上一篇文章最後介紹了APPEND方式插入臨時表，這種方式產生的UNDO和REDO都是最小的。

而且利用這種方式實現插入，還擁有普通表所不具備的優勢。

由於普通表執行APPEND方式插入，鎖表方式不再是行級鎖：

SQL> insert /*+ append */ into t_normal
  2  select rownum, object_name
  3  from dba_objects;

已建立49081行。

在其他的會話執行DML操作，則被鎖定：

SQL> set sqlp 'SQL2> '
SQL2> insert into t_normal
  2  values (1, 'a');

回到會話1：

SQL> select object_id              
  2  from dba_objects     
  3  where wner = user
  4  and object_name = 'T_NORMAL';

 OBJECT_ID
----------
     65319

SQL> select sid, type, id1, id2, lmode, request, ctime, block
  2  from v$lock
  3  where id1 = 65319;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       133 TM      65319          0          6          0        217          1
       134 TM      65319          0          0          3        202          0

SQL> commit;

提交完成。

可以看到表鎖資訊，直到會話1提交，會話2的DML才能繼續：

已建立 1 行。

SQL2> commit;

提交完成。

因此使用APPEND方式處理普通表需要謹慎，這會影響到其他會話對這個表的並非訪問。但是對於臨時表，這不是個問題：

SQL> insert /*+ append */ into t_temp
  2  select rownum, object_name
  3  from dba_objects;

已建立49081行。

在另外的會話執行：

SQL2> insert into t_temp
  2  values (1, 'a');

已建立 1 行。

SQL2> commit;

提交完成。

SQL2> update t_temp              
  2  set name = 'b';

已更新 1 行。

SQL2> delete t_temp;

已刪除 1 行。

SQL2> commit;

提交完成。

SQL2> truncate table t_temp;

表被截斷。

無論會話執行何種操作，都不會受到其他會話的影響。

對於臨時表而言，Oracle實現方式是為每個會話都實體化了一個臨時表，每個使用者都只會訪問自己對應的臨時表，因此不會影響到其他會話，也不會受其他會話影響，這也是為什麼臨時表的資料修改只有當前會話可以看到。因此當使用者執行truncate操作也只是影響自己會話的臨時表，而不會影響其他會話的修改結果。

回到會話1：

SQL> commit;

提交完成。

SQL> select count(*) from t_temp;

  COUNT(*)
----------
     49081

臨時表的APPEND方式插入還能避免另一個嚴重的問題，就是備份恢復的問題。如果資料庫級沒有設定FORCE LOGGING，那麼APPEND方式插入的資料是不寫入歸檔的，對於普通表空間而言，意味著操作結束後需要單獨備份，否則可能面臨資料丟失的危險，而對於臨時表則根本不存在這個問題，因為臨時表儲存在臨時表空間上，因此根本不會進行備份，而且如果發生資料庫的崩潰，臨時表的資料本來也不需要恢復。

臨時表使用APPEND方式的唯一缺點來自APPEND的另外一個特性，在提交前無法對該表進行查詢和修改：

SQL> insert /*+ append */ into t_temp
  2  select rownum, object_name
  3  from dba_objects;

已建立49081行。

SQL> select count(*) from t_temp;
select count(*) from t_temp
*
第 1 行出現錯誤:
ORA-12838: 無法在並行模式下修改之後讀/修改物件

SQL> insert into t_temp
  2  values (1, 'a');
insert into t_temp
            *
第 1 行出現錯誤:
ORA-12838: 無法在並行模式下修改之後讀/修改物件

SQL> commit;

提交完成。

SQL> select count(*) from t_temp;

  COUNT(*)
----------
     98162

這個限制和普通表沒有區別，但是對於COMMIT DETELE ROWS型別的臨時表則是致命的，這意味著這種型別的臨時表無法使用這個特性：

SQL> create global temporary table t_temp_del
  2  (id number, name varchar2(30))
  3  on commit delete rows;

表已建立。

SQL> insert /*+ append */ into t_temp_del
  2  select rownum, object_name
  3  from dba_objects;

已建立49082行。

SQL> select count(*) from t_temp_del;
select count(*) from t_temp_del
                     *
第 1 行出現錯誤:
ORA-12838: 無法在並行模式下修改之後讀/修改物件

SQL> commit;

提交完成。

SQL> select count(*) from t_temp_del;

  COUNT(*)
----------
         0