從一個開發(fā)需求的解決方案看Oracle臨時表
一、開發(fā)需求
最近有一個開發(fā)需求，大致需要先使用主表，或主表和幾張子表關(guān)聯(lián)查詢出 ID(主鍵) 及一些主表字段，然后再用這些 ID 查找最多 10 張表中對應(yīng)的記錄，主表記錄數(shù)大約 2000 萬，每張子表的記錄數(shù)均為百萬以上，最多可能會有 5000 萬，主表一條數(shù)據(jù)可能對應(yīng)子表多條數(shù)據(jù)。現(xiàn)在開發(fā)使用的邏輯是：
1. 使用條件查詢主表或主表和幾張子表 (不同場景) 符合條件的主表記錄 ID 值及其他一些主表字段項。
2. 利用這些主表 ID 值，分別和幾張子表使用 IN 子句，查詢出子表中符合條件的記錄項。有幾張子表，就執(zhí)行幾次 SQL 語句。
這么做的弊端是
由于 (1) 查出的 ID 值最多可能會有 100 個以上，因此子表使用 IN 子句的時候很有可能導(dǎo)致 CBO 選擇全表掃描，雖然從理論上說，一條 SQL 未必適用索引掃描效率就一定高，CBO 一定是基于現(xiàn)有的統(tǒng)計信息選擇一條成本值***的執(zhí)行計劃，但一張***甚至***的表，全表掃描的效率可想而知 (這兒我們不較真，可能通過 SSD、Exadata 硬件層面的使用能提高全表掃描的效率，此處只討論一般存儲條件下可行的方案)。另外，就是場景需要幾張子表，就會執(zhí)行幾次 SQL，一個場景下可能需要執(zhí)行很多次 SQL 語句。
綜合需求，可能至少有以下幾種改進方案
1. 使用一條 SQL 完成上述需求。
(1.1) 主表和所有子表采用 join 關(guān)聯(lián)的方式。
兩表兩表做 join，又由于主子表之間是一對多的關(guān)系，很可能造成結(jié)果集因為笛卡爾積變得很大，應(yīng)用處理出現(xiàn)內(nèi)存溢出的錯誤。
(1.2) 使用 union all 的方式關(guān)聯(lián)子表，作為 VIEW，然后和主表做關(guān)聯(lián)，這是羅大師推薦的方式，例如：
- 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) 的區(qū)別就是每一張子表的檢索都是一次獨立的索引唯一掃描，所有子表關(guān)聯(lián)后作為 VIEW，和主表做一次嵌套循環(huán)連接。但據(jù)了解，需求中每張子表的字段基本都不相同，有的子表選擇字段有幾十個，這么一來，使用這種 UNION ALL 需要檢索字段類型相同，開發(fā)拼接起來就比較費勁，不靈活。
2. 將 (1) 的結(jié)果集存入一張臨時表 (temporary table，不是應(yīng)用自行處理的普通表)，相當(dāng)于臨時結(jié)果集，每次子表都是和這張臨時表做兩表關(guān)聯(lián)查詢，這么做可以避免因為 IN 值太多導(dǎo)致的低效檢索，同時由于兩表關(guān)聯(lián)字段均為主鍵或外鍵 (設(shè)置索引)，可以使用索引掃描檢索，采用交易級別控制的臨時表，可以在完成本次交易后讓 Oracle 自動清空數(shù)據(jù)，同時 session 之間數(shù)據(jù)隔離。
3.(1) 不變，只是 (2) 中每次子表查詢，由應(yīng)用控制，例如每 30 個 IN 值執(zhí)行一條 SQL 語句，將一次子表查詢拆分為若干次查詢，好處是每次可以使用外鍵索引掃描檢索結(jié)果集，壞處就是無形中又多了 N 次 SQL 語句的執(zhí)行。
綜上三種方案，(1) 由于潛在的結(jié)果集過大的問題以及靈活性問題，被開發(fā)否了，目前采用的是方案 (3)，因為其對開發(fā)的改造較小，僅需要拆分 IN 語句，如果檢索效率較高，測試結(jié)論符合非功能要求，就采用這種方式，若不滿足要求，則會考慮使用方案 (2)。
就我來說，如果能滿足需求，方案 1 是***的，使用合適的索引完成一次檢索，減少了應(yīng)用和數(shù)據(jù)庫之間的交互次數(shù)，但可能這種業(yè)務(wù)需求確實很復(fù)雜，獲取信息方面確實要求比較高。其次是方案 2，雖然子表執(zhí)行 SQL 次數(shù)未變，但通過臨時表，可以保證每次檢索均可以使用索引快速定位，避免大表的全表掃描，同時臨時表特性對應(yīng)用幾乎透明。方案 3，唯一的好處就是避免了大表的全表掃描，但代價是會多一些 SQL 交互，至于究竟是否可以彌補性能上的差異，只能待性能測試的結(jié)論來看了。
如果各位對上述需求有更好的解決方案，或是上述方案仍有問題，還請不吝指正！
二、臨時表介紹和實驗
需要緩存中間結(jié)果集的場景，可以考慮使用臨時表，因為臨時表中的數(shù)據(jù)是 session 級別私有，每個 session 僅能看見和修改自己的數(shù)據(jù)，在 session 結(jié)束的時候，表中數(shù)據(jù)會被自動刪除，無需應(yīng)用操作。創(chuàng)建臨時表使用的是 CREATE GLOBAL TEMPORARY TABLE 語法，ON COMMIT 子句則決定了表數(shù)據(jù)是交易級別還是 session 級別，默認(rèn)是交易級別?？梢詫εR時表創(chuàng)建索引、視圖或觸發(fā)器。
ON COMMIT 子句的兩種參數(shù)區(qū)別如下：
臨時表中的數(shù)據(jù)默認(rèn)存儲于默認(rèn)的臨時表空間，可以創(chuàng)建過程中指定其他的臨時表空間。臨時表的數(shù)據(jù)和索引在定義的時候不會分配段，只有使用 INSERT(CTAS) 插入語句的時候，才會開始分配段空間。
創(chuàng)建交易級別臨時表：
- SQL> create global temporary table test (id number, name varchar2(10)) on commit delete rows;
查看表屬性，TEMPORARY 指定為 Y，說明是臨時表，沒有 tablespace_name 參數(shù)值，說明不是使用普通表空間存儲。
- SQL> select table_name, tablespace_name, temporary from dba_tables where owner='BISAL';
- TABLE_NAME TABLESPACE_NAME TEM
- ---------------- -------------------- ---
- TEST Y
session 1 執(zhí)行：
- SQL> insert into test values(1, 'a');
- SQL> select * from test;
- ID NAME
- -- ----
- 1 a
session 2 執(zhí)行：
- SQL> select * from test;
- no rows selected
說明臨時表數(shù)據(jù) session 級別隔離，
session 1 執(zhí)行：
- SQL> commit;
- SQL> select * from test;
- no rows selected
執(zhí)行 commit 結(jié)束交易，Oracle 會自動刪除臨時表中數(shù)據(jù)。
創(chuàng)建 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 執(zhí)行：
- SQL> insert into test values(1, 'a');
- SQL> select * from test;
- ID NAME
- -- ----
- 1 a
session 2 執(zhí)行：
- SQL> select * from test;
- no rows selected
session 1 執(zhí)行：
- SQL> commit;
- SQL> select * from test;
- ID NAME
- -- ----
- 1 a
執(zhí)行 commit 后，數(shù)據(jù)未刪除。退出當(dāng)前 session 再登陸，發(fā)現(xiàn)數(shù)據(jù)已被刪除了：
- SQL> select * from test;
- no rows selected
總結(jié)
臨時表使用起來其實很簡單，除了一些語法上和普通建表語句有些不同，對應(yīng)用來說就可以當(dāng)作普通表使用，但其實還是有一些細(xì)節(jié)需要注意：
1. 臨時表默認(rèn)使用的是默認(rèn)臨時表空間，如果應(yīng)用會有很多排序等需要耗費臨時表空間的場景，而且臨時表使用頻率很高，那么為了避免互相影響，可以考慮為臨時表建一個獨立的臨時表空間。
2. 如果使用 session 級別的臨時表，且應(yīng)用使用了連接池，則需要確保應(yīng)用完成一次交易過程中使用的是同一 session，避免違反臨時表使用規(guī)則。
