[[222506]]

一、開發需求 

最近有一個開發需求，大致需要先使用主表，或主表和幾張子表關聯查詢出 ID(主鍵) 及一些主表字段，然后再用這些 ID 查找最多 10 張表中對應的記錄，主表記錄數大約 2000 萬，每張子表的記錄數均為百萬以上，最多可能會有 5000 萬，主表一條數據可能對應子表多條數據。現在開發使用的邏輯是： 

1. 使用條件查詢主表或主表和幾張子表 (不同場景) 符合條件的主表記錄 ID 值及其他一些主表字段項。 

2. 利用這些主表 ID 值，分別和幾張子表使用 IN 子句，查詢出子表中符合條件的記錄項。有幾張子表，就執行幾次 SQL 語句。

這么做的弊端是

由于 (1) 查出的 ID 值最多可能會有 100 個以上，因此子表使用 IN 子句的時候很有可能導致 CBO 選擇全表掃描，雖然從理論上說，一條 SQL 未必適用索引掃描效率就一定高，CBO 一定是基于現有的統計信息選擇一條成本值***的執行計劃，但一張***甚至***的表，全表掃描的效率可想而知 (這兒我們不較真，可能通過 SSD、Exadata 硬件層面的使用能提高全表掃描的效率，此處只討論一般存儲條件下可行的方案)。另外，就是場景需要幾張子表，就會執行幾次 SQL，一個場景下可能需要執行很多次 SQL 語句。

綜合需求，可能至少有以下幾種改進方案

1. 使用一條 SQL 完成上述需求。 

(1.1) 主表和所有子表采用 join 關聯的方式。 

兩表兩表做 join，又由于主子表之間是一對多的關系，很可能造成結果集因為笛卡爾積變得很大，應用處理出現內存溢出的錯誤。 

(1.2) 使用 union all 的方式關聯子表，作為 VIEW，然后和主表做關聯，這是羅大師推薦的方式，例如：

SELECT A.ID, A.NAME   
FROM   
T_ZHUBIAO A,   
(SELECT ID, NAME FROM T_ZIBIAO1 UNION ALL SELECT ID, NAME FROM T_ZIBIAO2) B   
WHERE A.NAME = 'A' AND A.ID = B.ID;  

和 (1.1) 的區別就是每一張子表的檢索都是一次獨立的索引唯一掃描，所有子表關聯后作為 VIEW，和主表做一次嵌套循環連接。但據了解，需求中每張子表的字段基本都不相同，有的子表選擇字段有幾十個，這么一來，使用這種 UNION ALL 需要檢索字段類型相同，開發拼接起來就比較費勁，不靈活。 

2. 將 (1) 的結果集存入一張臨時表 (temporary table，不是應用自行處理的普通表)，相當于臨時結果集，每次子表都是和這張臨時表做兩表關聯查詢，這么做可以避免因為 IN 值太多導致的低效檢索，同時由于兩表關聯字段均為主鍵或外鍵 (設置索引)，可以使用索引掃描檢索，采用交易級別控制的臨時表，可以在完成本次交易后讓 Oracle 自動清空數據，同時 session 之間數據隔離。 

3.(1) 不變，只是 (2) 中每次子表查詢，由應用控制，例如每 30 個 IN 值執行一條 SQL 語句，將一次子表查詢拆分為若干次查詢，好處是每次可以使用外鍵索引掃描檢索結果集，壞處就是無形中又多了 N 次 SQL 語句的執行。

綜上三種方案，(1) 由于潛在的結果集過大的問題以及靈活性問題，被開發否了，目前采用的是方案 (3)，因為其對開發的改造較小，僅需要拆分 IN 語句，如果檢索效率較高，測試結論符合非功能要求，就采用這種方式，若不滿足要求，則會考慮使用方案 (2)。

就我來說，如果能滿足需求，方案 1 是***的，使用合適的索引完成一次檢索，減少了應用和數據庫之間的交互次數，但可能這種業務需求確實很復雜，獲取信息方面確實要求比較高。其次是方案 2，雖然子表執行 SQL 次數未變，但通過臨時表，可以保證每次檢索均可以使用索引快速定位，避免大表的全表掃描，同時臨時表特性對應用幾乎透明。方案 3，唯一的好處就是避免了大表的全表掃描，但代價是會多一些 SQL 交互，至于究竟是否可以彌補性能上的差異，只能待性能測試的結論來看了。

如果各位對上述需求有更好的解決方案，或是上述方案仍有問題，還請不吝指正！

二、臨時表介紹和實驗 

需要緩存中間結果集的場景，可以考慮使用臨時表，因為臨時表中的數據是 session 級別私有，每個 session 僅能看見和修改自己的數據，在 session 結束的時候，表中數據會被自動刪除，無需應用操作。創建臨時表使用的是 CREATE GLOBAL TEMPORARY TABLE 語法，ON COMMIT 子句則決定了表數據是交易級別還是 session 級別，默認是交易級別。可以對臨時表創建索引、視圖或觸發器。

ON COMMIT 子句的兩種參數區別如下： 

臨時表中的數據默認存儲于默認的臨時表空間，可以創建過程中指定其他的臨時表空間。臨時表的數據和索引在定義的時候不會分配段，只有使用 INSERT(CTAS) 插入語句的時候，才會開始分配段空間。

創建交易級別臨時表：

SQL> create global temporary table test (id number, name varchar2(10)) on commit delete rows; 

查看表屬性，TEMPORARY 指定為 Y，說明是臨時表，沒有 tablespace_name 參數值，說明不是使用普通表空間存儲。

SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL';  
TABLE_NAME  TABLESPACE_NAME  TEM  
---------------- --------------------        ---  
TEST                                            Y 

session 1 執行：

SQL> insert into test values(1, 'a');  
SQL> select * from test;  
ID NAME  
-- ----   
1 a 

session 2 執行：

SQL> select * from test;  
no rows selected 

說明臨時表數據 session 級別隔離，

session 1 執行：

SQL> commit;  
SQL> select * from test;  
no rows selected 

執行 commit 結束交易，Oracle 會自動刪除臨時表中數據。

創建 session 級臨時表：

SQL> create global temporary table test (id number, name varchar2(10)) on commit preserve rows; 

表屬性相同：

SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL';  
 
TABLE_NAME   TABLESPACE_NAME  TEM  
--------------     --------------------       ---  
TEST                                             Y 

session 1 執行：

SQL> insert into test values(1, 'a');  
SQL> select * from test;  
ID NAME  
-- ----   
1   a 

session 2 執行：

SQL> select * from test;  
no rows selected 

session 1 執行：

SQL> commit;  
SQL> select * from test;  
ID NAME  
-- ----   
1   a 

執行 commit 后，數據未刪除。退出當前 session 再登陸，發現數據已被刪除了：

SQL> select * from test;  
no rows selected 

總結

臨時表使用起來其實很簡單，除了一些語法上和普通建表語句有些不同，對應用來說就可以當作普通表使用，但其實還是有一些細節需要注意： 

1. 臨時表默認使用的是默認臨時表空間，如果應用會有很多排序等需要耗費臨時表空間的場景，而且臨時表使用頻率很高，那么為了避免互相影響，可以考慮為臨時表建一個獨立的臨時表空間。 

2. 如果使用 session 級別的臨時表，且應用使用了連接池，則需要確保應用完成一次交易過程中使用的是同一 session，避免違反臨時表使用規則。