Teradata 查詢指令 (3)

基本介紹

教學目標

初步了解 Teradata 中的暫存表種類及應用。

重點概念

暫存表是一種輔助工具,能夠提高 SQL 操作的效能,特別是針對非正規化的表格 (匯總、重複分組),或要求多條 SQL 語法完成,此外對於頻繁產生的中間結果或者作為後續工作基礎的中間結果也非常有用,Teradata 中主要有三種暫存表,包括全域暫存表 (Global Temporary Table) 、可變暫存表 (Volatile Temporary Table) 和衍生表 (Derived Table)

動機

一般來說,我們會創建永久資料表 (Permanent Table) 用於臨時儲存資料。

建立 SQL 語法

1
2
3
4
CREATE TABLE DAILY_NET_TXN
(ACCT_NBR INTEGER
,TOT_TXN_AMT DECIMAL(14,2))
UNIQUE PRIMARY INDEX (ACCT_NBR);

插入 SQL 語法

1
2
3
4
5
INSERT INTO DAILY_NET_TXN
SELECT ACCT_NBR
,SUM(TXN_AMT)
FROM NET_TXN
GROUP BY ACCT_NBR;

存取 SQL 語法

1
2
3
4
SELECT *
FROM DAILY_NET_TXN
WHERE ACCT_NBR IN
(20035223, 20024048, 20036699, 20045853)

執行結果

1
2
3
4
5
ACCT_NBR,TOT_TXN_AMT
20035223,-3280.00
20024048,-470.00
20036699,-870.00
20045853,-6910.00

雖然採用永久資料表能簡化 SQL 語句、不必要進行聚合操作、透過主索引存取回應時間快等優點,但也存在許多的缺點,像是採用不同的步驟皆需要建立資料表和載入資料,並且資料不需要時,需要手動刪除卻不能自動進行,會佔用永久空間 (Perm Space)

選擇

衍生資料表 (Derived Table)

  • 存在於整個查詢期間進行本機端查詢,查詢結束之後,資料表會被刪除。
  • 整合至 SQL 語法,當查詢完成後,Spool 緩衝區中的記錄會被刪除。
  • 在快取中保留資料表的定義,不採用資料字典,當系統重啟之後不存在,以減少系統負載。

可變暫存表 (Volatile Temporary Table)

  • 存在於整個 Session 期間,而非單次查詢,當 Session 結束會被刪除。
  • 整合至 SQL 語法,當查詢完成後,Spool 緩衝區中的記錄會被刪除。
  • 在快取中保留資料表的定義,不採用資料字典,當系統重啟之後不存在,以減少系統負載。

全域暫存表 (Global Temporay Table)

  • 存在於整個 Session 期間,並且每個使用者皆有專屬的實體,當 Session 結束會被刪除。
  • 空間主要是佔用使用者之臨時空間 (Temporary Space)
  • 在資料字典中創建永久資料表的定義,當系統重啟之後存在,以重複使用暫存表。

衝突

當使用者同時有兩個並行的 Session ,當按照以下的順序將會造成哪些問題,其中 CTV 代表創建可變暫存表,CTP 代表創建永久資料表。

Session 1 Session 2 執行結果
CTV T1 CTV T1 成功
CTP T2 成功
CTV T2 失敗,因為 T2 已經存在於 Perm Space 中。
CTV T3 成功
CTP T3 成功
INSERT T3 成功
CTV T3 失敗,因為 T3 已經存在於 Perm Space 中。
INSERT T3 成功
DROP T3 成功
SELECT * FROM T3 成功

相關資源