[[222506]]

一、開(kāi)發(fā)需求 

最近有一個(gè)開(kāi)發(fā)需求，大致需要先使用主表，或主表和幾張子表關(guān)聯(lián)查詢出 ID(主鍵) 及一些主表字段，然后再用這些 ID 查找最多 10 張表中對(duì)應(yīng)的記錄，主表記錄數(shù)大約 2000 萬(wàn)，每張子表的記錄數(shù)均為百萬(wàn)以上，最多可能會(huì)有 5000 萬(wàn)，主表一條數(shù)據(jù)可能對(duì)應(yīng)子表多條數(shù)據(jù)。現(xiàn)在開(kāi)發(fā)使用的邏輯是： 

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

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

這么做的弊端是

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

綜合需求，可能至少有以下幾種改進(jìn)方案

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

(1.1) 主表和所有子表采用 join 關(guān)聯(lián)的方式。 

兩表兩表做 join，又由于主子表之間是一對(duì)多的關(guān)系，很可能造成結(jié)果集因?yàn)榈芽柗e變得很大，應(yīng)用處理出現(xiàn)內(nèi)存溢出的錯(cuò)誤。 

(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ū)別就是每一張子表的檢索都是一次獨(dú)立的索引唯一掃描，所有子表關(guān)聯(lián)后作為 VIEW，和主表做一次嵌套循環(huán)連接。但據(jù)了解，需求中每張子表的字段基本都不相同，有的子表選擇字段有幾十個(gè)，這么一來(lái)，使用這種 UNION ALL 需要檢索字段類型相同，開(kāi)發(fā)拼接起來(lái)就比較費(fèi)勁，不靈活。 

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

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

綜上三種方案，(1) 由于潛在的結(jié)果集過(guò)大的問(wèn)題以及靈活性問(wèn)題，被開(kāi)發(fā)否了，目前采用的是方案 (3)，因?yàn)槠鋵?duì)開(kāi)發(fā)的改造較小，僅需要拆分 IN 語(yǔ)句，如果檢索效率較高，測(cè)試結(jié)論符合非功能要求，就采用這種方式，若不滿足要求，則會(huì)考慮使用方案 (2)。

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

如果各位對(duì)上述需求有更好的解決方案，或是上述方案仍有問(wèn)題，還請(qǐng)不吝指正！

二、臨時(shí)表介紹和實(shí)驗(yàn) 

需要緩存中間結(jié)果集的場(chǎng)景，可以考慮使用臨時(shí)表，因?yàn)榕R時(shí)表中的數(shù)據(jù)是 session 級(jí)別私有，每個(gè) session 僅能看見(jiàn)和修改自己的數(shù)據(jù)，在 session 結(jié)束的時(shí)候，表中數(shù)據(jù)會(huì)被自動(dòng)刪除，無(wú)需應(yīng)用操作。創(chuàng)建臨時(shí)表使用的是 CREATE GLOBAL TEMPORARY TABLE 語(yǔ)法，ON COMMIT 子句則決定了表數(shù)據(jù)是交易級(jí)別還是 session 級(jí)別，默認(rèn)是交易級(jí)別。可以對(duì)臨時(shí)表創(chuàng)建索引、視圖或觸發(fā)器。

ON COMMIT 子句的兩種參數(shù)區(qū)別如下： 

臨時(shí)表中的數(shù)據(jù)默認(rèn)存儲(chǔ)于默認(rèn)的臨時(shí)表空間，可以創(chuàng)建過(guò)程中指定其他的臨時(shí)表空間。臨時(shí)表的數(shù)據(jù)和索引在定義的時(shí)候不會(huì)分配段，只有使用 INSERT(CTAS) 插入語(yǔ)句的時(shí)候，才會(huì)開(kāi)始分配段空間。

創(chuàng)建交易級(jí)別臨時(shí)表：

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

查看表屬性，TEMPORARY 指定為 Y，說(shuō)明是臨時(shí)表，沒(méi)有 tablespace_name 參數(shù)值，說(shuō)明不是使用普通表空間存儲(chǔ)。

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 

說(shuō)明臨時(shí)表數(shù)據(jù) session 級(jí)別隔離，

session 1 執(zhí)行：

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

執(zhí)行 commit 結(jié)束交易，Oracle 會(huì)自動(dòng)刪除臨時(shí)表中數(shù)據(jù)。

創(chuàng)建 session 級(jí)臨時(shí)表：

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é)

臨時(shí)表使用起來(lái)其實(shí)很簡(jiǎn)單，除了一些語(yǔ)法上和普通建表語(yǔ)句有些不同，對(duì)應(yīng)用來(lái)說(shuō)就可以當(dāng)作普通表使用，但其實(shí)還是有一些細(xì)節(jié)需要注意： 

1. 臨時(shí)表默認(rèn)使用的是默認(rèn)臨時(shí)表空間，如果應(yīng)用會(huì)有很多排序等需要耗費(fèi)臨時(shí)表空間的場(chǎng)景，而且臨時(shí)表使用頻率很高，那么為了避免互相影響，可以考慮為臨時(shí)表建一個(gè)獨(dú)立的臨時(shí)表空間。 

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