一個開發需求的解決方案 & Oracle臨時表介紹

一、開發需求

最近有一個開發需求，大緻需要先使用主表，或主表和幾張子表關聯查詢出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，然後和主表做關聯，這是羅大師推薦的方式，例如：

和(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)插入語句的時候，才會開始配置設定段空間。

建立交易級别臨時表：

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

session 1執行：

session 2執行：

說明臨時表資料session級别隔離，

執行commit結束交易，Oracle會自動删除臨時表中資料。

建立session級臨時表：

表屬性相同：

執行commit後，資料未删除。退出目前session再登陸，發現資料已被删除了：

總結：

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

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

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