資料庫表--temporary table

用於儲存事物或會話期間的中間結果集，資料只對當前會話可見，所有會話看不到其他會話的資料，即使會話已經提交，因此不存在使用者併發問題。建立臨時表的動作不涉及儲存空間的分配，不會為此分配初始區段，當一個會話第一次在臨時表中放入資料時，才會為該會話建立一個臨時段。每個使用者應該有自己的臨時表空間，並在自己的臨時表空間中分配臨時段。

基於會話的臨時表（會話斷開之前，資料一直存在，只有當前會話能看到這些資料，與提交無關）
JEL@JEL >create global temporary table temp_table_session
  2  on commit preserve rows
  3  as
  4  select * from all_objects where 0=1;

Table created.


基於事物的臨時表（會話一提交，資料就被清除了，在臨時表的自動清除過程中不存在開銷）
JEL@JEL >create global temporary table temp_table_transaction
  2  on commit delete rows
  3  as
  4  select * from all_objects where 0=1;

Table created.


提交前：
JEL@JEL >insert into temp_table_session select * from all_objects;

9303 rows created.

JEL@JEL >insert into temp_table_transaction select * from all_objects;

9303 rows created.

JEL@JEL >select session_cnt,transaction_cnt
  2  from (select count(*) session_cnt from temp_table_session),
  3  (select count(*) transaction_cnt from temp_table_transaction);

SESSION_CNT TRANSACTION_CNT
----------- ---------------
       9303            9303

提交後：
JEL@JEL >commit;

Commit complete.

JEL@JEL >select session_cnt,transaction_cnt from (select count(*) session_cnt from temp_table_session),(select count(*) transaction_cnt from temp_table_transaction);

SESSION_CNT TRANSACTION_CNT
----------- ---------------
       9303               0

開始新會話後：
JEL@JEL >conn / as sysdba
Connected.
SYS@JEL >conn jel/jel
Connected.
JEL@JEL >select session_cnt,transaction_cnt from (select count(*) session_cnt from temp_table_session),(select count(*) transaction_cnt from temp_table_transaction);

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          0               0


注意：將所有全域性臨時表只建立一次，作為應用安裝的一部分，就像建立永久表一樣。
歸根到底，目標是，臨時表應該在應用安裝期間建立，絕對不要在執行時建立。
臨時表可以有觸發器、檢查約束、索引等，但不支援永久表的某些特性，如：
1、不能有引用完整性約束，不能作為外來鍵的目標，也不能再臨時表中定義外來鍵
2、不能有nested table型別的列
3、不能是IOT
4、不能再任何型別的聚簇中
5、不能分割槽
6、不能透過analyze表命令生成統計資訊

臨時表的缺點之一就是最佳化器不能正常的得到臨時表的真實統計。在許多情況下，正確的解決方案並不是使用臨時表，而是使用一個inline view。如果應用中需要臨時儲存一個行集由其他表處理，臨時表就很有用。


向最佳化器提供關於全域性臨時表的統計資訊：
方法1：動態取樣
oracle10g中，預設會發生自動取樣
JEL@JEL >select * from temp_table_session;

Execution Plan
----------------------------------------------------------
Plan hash value: 3237906844

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

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT  |                    |  9943 |  1242K|    34   (0)| 00
:00:01 |

|   1 |  TABLE ACCESS FULL| TEMP_TABLE_SESSION |  9943 |  1242K|    34   (0)| 00
:00:01 |

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


Note
-----
   - dynamic sampling used for this statement ---此次提示應用了dynamic sampling

方法2：dbms_stats使用者分析（gather_schema_stats、gather_table_stats）,在on commit delete rows表上不可行
SYS@JEL >conn jel/jel
Connected.
JEL@JEL >insert into temp_table_session select * from all_objects;

9303 rows created.

JEL@JEL >insert into temp_table_transaction select * from all_objects;

9303 rows created.

JEL@JEL >exec dbms_stats.gather_schema_stats('JEL');

PL/SQL procedure successfully completed.

JEL@JEL >select table_name,last_analyzed,num_rows from user_tables;

TABLE_NAME                     LAST_ANAL   NUM_ROWS
------------------------------ --------- ----------
DEPT_AND_EMP                   11-DEC-13          2
TEMP_TABLE_TRANSACTION
TEMP_TABLE_SESSION

以上統計兩個全域性臨時表被忽略。
JEL@JEL >exec dbms_stats.gather_schema_stats('JEL',gather_temp=>true);

PL/SQL procedure successfully completed.

JEL@JEL >select table_name,last_analyzed,num_rows from user_tables;

TABLE_NAME                     LAST_ANAL   NUM_ROWS
------------------------------ --------- ----------
DEPT_AND_EMP                   11-DEC-13          2
TEMP_TABLE_TRANSACTION         11-DEC-13          0
TEMP_TABLE_SESSION             11-DEC-13       9303

以上統計TEMP_TABLE_SESSION表有正確的統計結果，但TEMP_TABLE_TRANSACTION表沒有，這是因為dbms_stats將提交，而擦除表中的所有資訊。

注意：第一，要保證在收集統計資訊的會話中用代表性資料填充全域性臨時表；第二，如果有on commit delete rows全域性臨時表，就不應該用此方法

方法3：透過一個手動過程用臨時表的代表性統計資訊填充資料字典

例如：如果平均臨時表中行數是500，行的平均大小是100位元組，塊數為7
JEL@JEL >begin
  2  dbms_stats.set_table_stats(ownname=>'JEL',tabname=>'T',numrows=>500,numblks=>7,avgrlen=>100);
  3  end;
  4  /

PL/SQL procedure successfully completed.

JEL@JEL >select table_name,num_rows,blocks,avg_row_len from user_tables where table_name='T';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T                                     500          7         100

現在，最佳化器不會使用自己的最優猜測，而會使用我們給出的統計。

臨時表的使用場景
1、迴圈SQL，最初最佳化前，系統中有很多類似的sql迴圈執行，效率很低。比如通常會有透過介面傳入數千的引數，然後根據這些引數迴圈執行sql。最佳化時，可先把這些引數insert到臨時表，然後關聯該臨時表一次執行以達到最佳化的效果。
2、多表關聯，利用臨時表簡化有太多表關聯的複雜SQL
3、如果某個資料集會重複多次使用的情況下建議使用臨時表
4、臨時表在邏輯複雜的大資料量更新的時候很有用，查詢部分with就可以了
5、臨時表作為複雜查詢條件的中間結果用於主查詢