Teradata 快速指南

---

---

Teradata - 簡介

什麼是 Teradata？

Teradata 是流行的關係型資料庫管理系統之一。它主要適用於構建大型資料倉庫應用程式。Teradata 透過並行處理的概念實現這一點。它是由 Teradata 公司開發的。

Teradata 的歷史

以下是 Teradata 歷史的簡要概述，列出了主要的里程碑。

• 1979 − Teradata 成立。

• 1984 − 釋出首個數據庫計算機 DBC/1012。

• 1986 − 《財富》雜誌將 Teradata 評為“年度產品”。

• 1999 − 使用 Teradata 的全球最大資料庫，擁有 130TB 資料。

• 2002 − Teradata V2R5 釋出，包含分割槽主索引和壓縮功能。

• 2006 − 推出 Teradata 主資料管理解決方案。

• 2008 − Teradata 13.0 釋出，包含主動資料倉庫功能。

• 2011 − 收購 Teradata Aster，進軍高階分析領域。

• 2012 − Teradata 14.0 釋出。

• 2014 − Teradata 15.0 釋出。

Teradata 的特性

以下是 Teradata 的一些特性：

• 無限並行性 − Teradata 資料庫系統基於大規模並行處理 (MPP) 架構。MPP 架構將工作負載均勻地分配到整個系統。Teradata 系統將任務分配到其程序中，並並行執行它們，以確保任務快速完成。

• 共享無架構 − Teradata 的架構稱為共享無架構。Teradata 節點、其訪問模組處理器 (AMP) 和與 AMP 關聯的磁碟獨立工作。它們不與其他共享。

• 線性可擴充套件性 − Teradata 系統具有高度可擴充套件性。它們可以擴充套件到 2048 個節點。例如，您可以透過將 AMP 數量加倍來使系統的容量加倍。

• 連線性 − Teradata 可以連線到通道連線系統（例如大型機）或網路連線系統。

• 成熟的最佳化器 − Teradata 最佳化器是市場上最成熟的最佳化器之一。它從一開始就被設計為並行的，並且在每個版本中都得到了改進。

• SQL − Teradata 支援行業標準 SQL 來與儲存在表中的資料互動。除此之外，它還提供自己的擴充套件。

• 強大的實用程式 − Teradata 提供強大的實用程式來匯入/匯出 Teradata 系統中的資料，例如 FastLoad、MultiLoad、FastExport 和 TPT。

• 自動分佈 − Teradata 自動將資料均勻地分佈到磁碟，無需任何手動干預。

Teradata - 安裝

Teradata 為 VMware 提供 Teradata Express，這是一個功能齊全的 Teradata 虛擬機器。它提供高達 1 TB 的儲存空間。Teradata 提供 40GB 和 1TB 兩個版本的 VMware。

先決條件

由於虛擬機器是 64 位的，因此您的 CPU 必須支援 64 位。

Windows 安裝步驟

步驟 1 − 從以下連結下載所需的 VM 版本： https://downloads.teradata.com/download/database/teradata-express-for-vmware-player

步驟 2 − 解壓縮檔案並指定目標資料夾。

步驟 3 − 從以下連結下載 VMware Workstation Player： https://my.vmware.com/web/vmware/downloads。它適用於 Windows 和 Linux。下載適用於 Windows 的 VMware Workstation Player。

步驟 4 − 下載完成後，安裝軟體。

步驟 5 − 安裝完成後，執行 VMware 客戶端。

步驟 6 − 選擇“開啟虛擬機器”。瀏覽解壓縮的 Teradata VMware 資料夾，然後選擇副檔名為 .vmdk 的檔案。

步驟 7 − Teradata VMware 已新增到 VMware 客戶端。選擇新增的 Teradata VMware 並單擊“播放虛擬機器”。

步驟 8 − 如果出現軟體更新彈出視窗，您可以選擇“稍後提醒我”。

步驟 9 − 輸入使用者名稱 root，按 Tab 鍵，然後輸入密碼 root，再次按 Enter 鍵。

步驟 10 − 桌面出現以下屏幕後，雙擊“root 的主目錄”。然後雙擊“Genome 的終端”。這將開啟 Shell。

步驟 11 − 在以下 Shell 中，輸入命令 /etc/init.d/tpa start。這將啟動 Teradata 伺服器。

啟動 BTEQ

BTEQ 實用程式用於互動式提交 SQL 查詢。以下是啟動 BTEQ 實用程式的步驟。

步驟 1 − 輸入命令 /sbin/ifconfig 並記下 VMware 的 IP 地址。

步驟 2 − 執行命令 bteq。在登入提示符下，輸入命令。

Logon <ipaddress>/dbc,dbc; 然後按Enter鍵。在密碼提示符下，輸入密碼 dbc;

您可以使用 BTEQ 登入 Teradata 系統並執行任何 SQL 查詢。

Teradata - 架構

Teradata 架構基於大規模並行處理 (MPP) 架構。Teradata 的主要元件是解析引擎、BYNET 和訪問模組處理器 (AMP)。下圖顯示了 Teradata 節點的頂層架構。

Teradata 的元件

Teradata 的關鍵元件如下：

• 節點 − 它是 Teradata 系統中的基本單元。Teradata 系統中的每個單獨伺服器都稱為節點。節點包含其自己的作業系統、CPU、記憶體、Teradata RDBMS 軟體的副本以及磁碟空間。機櫃包含一個或多個節點。

• 解析引擎 − 解析引擎負責接收來自客戶端的查詢並準備有效的執行計劃。解析引擎的職責是：

  • 接收來自客戶端的 SQL 查詢

  • 解析 SQL 查詢並檢查語法錯誤

  • 檢查使用者是否對 SQL 查詢中使用的物件具有所需的許可權

  • 檢查 SQL 中使用的物件是否實際存在

  • 準備執行 SQL 查詢的執行計劃並將其傳遞給 BYNET

  • 接收來自 AMP 的結果併發送給客戶端

• 訊息傳遞層 − 訊息傳遞層稱為 BYNET，是 Teradata 系統中的網路層。它允許 PE 和 AMP 之間以及節點之間的通訊。它接收來自解析引擎的執行計劃併發送給 AMP。類似地，它接收來自 AMP 的結果併發送給解析引擎。

• 訪問模組處理器 (AMP) − AMP（稱為虛擬處理器 (vproc)）是實際儲存和檢索資料的元件。AMP 接收來自解析引擎的資料和執行計劃，執行任何資料型別轉換、聚合、過濾、排序並將資料儲存在其關聯的磁碟中。表中的記錄均勻地分佈在系統中的 AMP 之間。每個 AMP 都與一組儲存資料的磁碟相關聯。只有該 AMP 才能讀取/寫入磁碟上的資料。

儲存架構

當客戶端執行查詢以插入記錄時，解析引擎將記錄傳送到 BYNET。BYNET 檢索記錄並將行傳送到目標 AMP。AMP 將這些記錄儲存在其磁碟上。下圖顯示了 Teradata 的儲存架構。

檢索架構

當客戶端執行查詢以檢索記錄時，解析引擎會向 BYNET 傳送請求。BYNET 將檢索請求傳送到相應的 AMP。然後 AMP 並行搜尋其磁碟並識別所需的記錄，並將其傳送到 BYNET。然後 BYNET 將記錄傳送到解析引擎，解析引擎又將記錄傳送到客戶端。以下是 Teradata 的檢索架構。

Teradata - 關係型概念

關係型資料庫管理系統 (RDBMS) 是一種 DBMS 軟體，有助於與資料庫互動。它們使用結構化查詢語言 (SQL) 與儲存在表中的資料互動。

資料庫

資料庫是邏輯相關的資料的集合。許多使用者出於不同的目的訪問它們。例如，銷售資料庫包含儲存在許多表中的關於銷售的全部資訊。

表

表是 RDBMS 中儲存資料的基本單元。表是行和列的集合。以下是一個員工表的示例。

列

列包含類似的資料。例如，員工表中的 BirthDate 列包含所有員工的出生日期資訊。

行

行是所有列的一個例項。例如，在員工表中，一行包含單個員工的資訊。

主鍵

主鍵用於唯一標識表中的一行。主鍵列不允許重複值，也不能接受 NULL 值。它是表中的必填欄位。

外部索引鍵

外部索引鍵用於在表之間建立關係。子表中的外部索引鍵定義為父表中的主鍵。一個表可以有多個外部索引鍵。它可以接受重複值和 NULL 值。外部索引鍵在表中是可選的。

Teradata - 資料型別

表中的每一列都與一個數據型別相關聯。資料型別指定將在列中儲存哪種值。Teradata 支援多種資料型別。以下是某些常用資料型別。

Teradata - 表

關係模型中的表定義為資料的集合。它們表示為行和列。

表型別

型別 Teradata 支援不同型別的表。

• **永久表** - 這是預設表，它包含使用者插入的資料並永久儲存資料。

• **臨時表** - 插入臨時表中的資料僅在使用者會話期間保留。會話結束時，表和資料將被刪除。這些表主要用於在資料轉換期間儲存中間資料。

• **全域性臨時表** - 全域性臨時表的定義是持久的，但表中的資料在使用者會話結束時會被刪除。

• **派生表** - 派生表儲存查詢中的中間結果。它們的生存期在建立、使用和刪除它們的查詢內。

集合與多集

Teradata 根據處理重複記錄的方式將表分類為集合表或多集表。定義為集合表的表不儲存重複記錄，而多集表可以儲存重複記錄。

Teradata - 資料操作

本章介紹用於操作 Teradata 表中儲存的資料的 SQL 命令。

插入記錄

INSERT INTO 語句用於將記錄插入表中。

語法

以下是 INSERT INTO 的通用語法。

INSERT INTO <tablename> 
(column1, column2, column3,…) 
VALUES 
(value1, value2, value3 …);

示例

以下示例將記錄插入員工表。

INSERT INTO Employee (
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate, 
   DepartmentNo 
)
VALUES ( 
   101, 
   'Mike', 
   'James', 
   '1980-01-05', 
   '2005-03-27', 
   01
);

插入上述查詢後，可以使用 SELECT 語句查看錶中的記錄。

從另一個表插入

INSERT SELECT 語句用於從另一個表插入記錄。

語法

以下是 INSERT INTO 的通用語法。

INSERT INTO <tablename> 
(column1, column2, column3,…) 
SELECT 
column1, column2, column3… 
FROM  
<source table>;

示例

以下示例將記錄插入員工表。在執行以下插入查詢之前，建立一個名為 Employee_Bkup 的表，其列定義與員工表相同。

INSERT INTO Employee_Bkup ( 
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate, 
   DepartmentNo 
) 
SELECT 
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate,
   DepartmentNo 
FROM  
   Employee;

執行上述查詢後，它將員工表中的所有記錄插入 employee_bkup 表。

規則

• VALUES 列表中指定的列數應與 INSERT INTO 子句中指定的列數匹配。

• NOT NULL 列的值是必填的。

• 如果未指定值，則為可為空欄位插入 NULL。

• VALUES 子句中指定的列的資料型別應與 INSERT 子句中列的資料型別相容。

更新記錄

UPDATE 語句用於更新表中的記錄。

語法

以下是 UPDATE 的通用語法。

UPDATE <tablename> 
SET <columnnamme> = <new value> 
[WHERE condition];

示例

以下示例將員工 101 的員工部門更新為 03。

UPDATE Employee 
SET DepartmentNo = 03 
WHERE EmployeeNo = 101;

在以下輸出中，您可以看到員工編號 101 的 DepartmentNo 已從 1 更新為 3。

SELECT Employeeno, DepartmentNo FROM Employee; 
*** Query completed. One row found. 2 columns returned. 
*** Total elapsed time was 1 second.  
EmployeeNo    DepartmentNo 
-----------  ------------- 
   101             3 

規則

• 您可以更新表的一個或多個值。

• 如果未指定 WHERE 條件，則表的所有行都會受到影響。

• 您可以使用另一個表中的值更新表。

刪除記錄

DELETE FROM 語句用於更新表中的記錄。

語法

以下是 DELETE FROM 的通用語法。

DELETE FROM  <tablename> 
[WHERE condition];

示例

以下示例從員工表中刪除員工 101。

DELETE FROM Employee 
WHERE EmployeeNo = 101;

在以下輸出中，您可以看到員工 101 已從表中刪除。

SELECT EmployeeNo FROM Employee;  
*** Query completed. No rows found. 
*** Total elapsed time was 1 second. 

規則

• 您可以更新表的一個或多個記錄。

• 如果未指定 WHERE 條件，則將刪除表的所有行。

• 您可以使用另一個表中的值更新表。

Teradata - SELECT 語句

SELECT 語句用於從表中檢索記錄。

語法

以下是 SELECT 語句的基本語法。

SELECT 
column 1, column 2, ..... 
FROM  
tablename;

示例

考慮以下員工表。

以下是 SELECT 語句的示例。

SELECT EmployeeNo,FirstName,LastName 
FROM Employee;

執行此查詢時，它將從員工表中提取 EmployeeNo、FirstName 和 LastName 列。

 EmployeeNo            FirstName                       LastName 
-----------  ------------------------------  --------------------------- 
   101                   Mike                            James 
   104                   Alex                            Stuart 
   102                   Robert                          Williams 
   105                   Robert                          James 
   103                   Peter                           Paul

如果要從表中提取所有列，可以使用以下命令，而無需列出所有列。

SELECT * FROM Employee;

上述查詢將從員工表中提取所有記錄。

WHERE 子句

WHERE 子句用於篩選 SELECT 語句返回的記錄。WHERE 子句與一個條件相關聯。只有滿足 WHERE 子句中條件的記錄才會被返回。

語法

以下是帶有 WHERE 子句的 SELECT 語句的語法。

SELECT * FROM tablename 
WHERE[condition];

示例

以下查詢提取 EmployeeNo 為 101 的記錄。

SELECT * FROM Employee 
WHERE EmployeeNo = 101;

執行此查詢時，它將返回以下記錄。

 EmployeeNo          FirstName                      LastName 
----------- ------------------------------ ----------------------------- 
   101                 Mike                           James 

ORDER BY

執行 SELECT 語句時，返回的行沒有任何特定順序。ORDER BY 子句用於按任何列的升序/降序排列記錄。

語法

以下是帶有 ORDER BY 子句的 SELECT 語句的語法。

SELECT * FROM tablename 
ORDER BY column 1, column 2..;

示例

以下查詢從員工表中提取記錄，並按 FirstName 排序結果。

SELECT * FROM Employee 
ORDER BY FirstName;

執行上述查詢時，將產生以下輸出。

 EmployeeNo         FirstName                      LastName 
----------- ------------------------------ ----------------------------- 
    104               Alex                           Stuart 
    101               Mike                           James 
    103               Peter                          Paul 
    102               Robert                         Williams 
    105               Robert                         James 

GROUP BY

GROUP BY 子句與 SELECT 語句一起使用，並將相似的記錄排列成組。

語法

以下是帶有 GROUP BY 子句的 SELECT 語句的語法。

SELECT column 1, column2 …. FROM tablename 
GROUP BY column 1, column 2..;

示例

以下示例按 DepartmentNo 列對記錄進行分組，並標識每個部門的總數。

SELECT DepartmentNo,Count(*) FROM   
Employee 
GROUP BY DepartmentNo;

執行上述查詢時，將產生以下輸出。

 DepartmentNo    Count(*) 
------------  ----------- 
     3             1 
     1             1 
     2             3 

Teradata - 邏輯和條件運算子

Teradata 支援以下邏輯和條件運算子。這些運算子用於執行比較和組合多個條件。

BETWEEN

BETWEEN 命令用於檢查值是否在值的範圍內。

示例

考慮以下員工表。

以下示例提取員工編號在 101、102 和 103 範圍內的記錄。

SELECT EmployeeNo, FirstName FROM  
Employee 
WHERE EmployeeNo BETWEEN 101 AND 103;

執行上述查詢時，它將返回員工編號在 101 和 103 之間的員工記錄。

*** Query completed. 3 rows found. 2 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo            FirstName 
-----------  ------------------------------ 
   101                   Mike 
   102                   Robert 
   103                   Peter

IN

IN 命令用於根據給定的值列表檢查值。

示例

以下示例提取員工編號為 101、102 和 103 的記錄。

SELECT EmployeeNo, FirstName FROM  
Employee 
WHERE EmployeeNo in (101,102,103);

上述查詢返回以下記錄。

*** Query completed. 3 rows found. 2 columns returned. 
*** Total elapsed time was 1 second.  
 EmployeeNo            FirstName 
-----------  ------------------------------ 
   101                   Mike 
   102                   Robert 
   103                   Peter

NOT IN

NOT IN 命令反轉 IN 命令的結果。它提取與給定列表不匹配的值的記錄。

示例

以下示例提取員工編號不為 101、102 和 103 的記錄。

SELECT * FROM  
Employee 
WHERE EmployeeNo not in (101,102,103);

上述查詢返回以下記錄。

*** Query completed. 2 rows found. 6 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo          FirstName                      LastName 
----------- ------------------------------ -----------------------------    
    104                Alex                          Stuart 
    105                Robert                        James 

Teradata - 集合運算子

SET 運算子組合來自多個 SELECT 語句的結果。這可能類似於連線，但連線組合來自多個表的列，而 SET 運算子組合來自多個行的行。

規則

• 每個 SELECT 語句的列數應相同。

• 每個 SELECT 的資料型別必須相容。

• ORDER BY 應僅包含在最終的 SELECT 語句中。

UNION

UNION 語句用於組合來自多個 SELECT 語句的結果。它忽略重複項。

語法

以下是 UNION 語句的基本語法。

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
UNION  

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

示例

考慮以下員工表和薪資表。

以下 UNION 查詢組合來自 Employee 和 Salary 表的 EmployeeNo 值。

SELECT EmployeeNo 
FROM  
Employee 
UNION 

SELECT EmployeeNo 
FROM  
Salary;

執行查詢後，它將產生以下輸出。

EmployeeNo 
----------- 
   101 
   102 
   103 
   104 
   105

UNION ALL

UNION ALL 語句類似於 UNION，它組合來自多個表的結果，包括重複行。

語法

以下是 UNION ALL 語句的基本語法。

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
UNION ALL 

SELECT col1, col2, col3…
FROM  
<table 2> 
[WHERE condition];

示例

以下是 UNION ALL 語句的示例。

SELECT EmployeeNo 
FROM  
Employee 
UNION ALL 

SELECT EmployeeNo 
FROM  
Salary;

執行上述查詢後，它將產生以下輸出。您可以看到它也返回重複項。

 EmployeeNo 
----------- 
    101 
    104 
    102 
    105 
    103 
    101 
    104 
    102 
    103

INTERSECT

INTERSECT 命令也用於組合來自多個 SELECT 語句的結果。它返回第一個 SELECT 語句中與第二個 SELECT 語句中具有相應匹配的行。換句話說，它返回同時存在於兩個 SELECT 語句中的行。

語法

以下是 INTERSECT 語句的基本語法。

SELECT col1, col2, col3… 
FROM  
<table 1>
[WHERE condition] 
INTERSECT 

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

示例

以下是 INTERSECT 語句的示例。它返回同時存在於兩個表中的 EmployeeNo 值。

SELECT EmployeeNo 
FROM  
Employee 
INTERSECT 

SELECT EmployeeNo 
FROM  
Salary; 

執行上述查詢後，它將返回以下記錄。EmployeeNo 105 被排除在外，因為它不存在於 SALARY 表中。

EmployeeNo 
----------- 
   101 
   104 
   102 
   103 

MINUS/EXCEPT

MINUS/EXCEPT 命令組合來自多個表的行，並返回第一個 SELECT 中但不在第二個 SELECT 中的行。兩者都返回相同的結果。

語法

以下是 MINUS 語句的基本語法。

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
MINUS 

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

示例

以下是 MINUS 語句的示例。

SELECT EmployeeNo 
FROM  
Employee 
MINUS 

SELECT EmployeeNo 
FROM  
Salary;

執行此查詢時，它將返回以下記錄。

EmployeeNo 
----------- 
   105 

Teradata - 字串操作

Teradata 提供多個函式來操作字串。這些函式與 ANSI 標準相容。

示例

下表列出了一些字串函式及其結果。

Teradata - 日期/時間函式

本章討論Teradata中可用的日期/時間函式。

日期儲存

日期在內部以整數形式儲存，使用以下公式。

((YEAR - 1900) * 10000) + (MONTH * 100) + DAY

您可以使用以下查詢來檢查日期的儲存方式。

SELECT CAST(CURRENT_DATE AS INTEGER);

由於日期儲存為整數，因此您可以對其執行一些算術運算。Teradata提供函式來執行這些運算。

EXTRACT

EXTRACT函式從DATE值中提取日、月和年的部分。此函式還用於從TIME/TIMESTAMP值中提取小時、分鐘和秒。

示例

以下示例顯示如何從日期和時間戳值中提取年、月、日、時、分和秒值。

SELECT EXTRACT(YEAR FROM CURRENT_DATE);  
EXTRACT(YEAR FROM Date) 
----------------------- 
        2016  
SELECT EXTRACT(MONTH FROM CURRENT_DATE);  
EXTRACT(MONTH FROM Date) 
------------------------ 
          1        
SELECT EXTRACT(DAY FROM CURRENT_DATE);  
EXTRACT(DAY FROM Date) 
------------------------ 
          1    
       
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);  
EXTRACT(HOUR FROM Current TimeStamp(6)) 
--------------------------------------- 
                 4      
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP);  
EXTRACT(MINUTE FROM Current TimeStamp(6)) 
----------------------------------------- 
                 54  
SELECT EXTRACT(SECOND FROM CURRENT_TIMESTAMP);  
EXTRACT(SECOND FROM Current TimeStamp(6)) 
----------------------------------------- 
              27.140000

INTERVAL

Teradata提供INTERVAL函式來對DATE和TIME值執行算術運算。INTERVAL函式有兩種型別。

年-月間隔

• YEAR
• YEAR TO MONTH
• MONTH

日-時間隔

• DAY
• DAY TO HOUR
• DAY TO MINUTE
• DAY TO SECOND
• HOUR
• HOUR TO MINUTE
• HOUR TO SECOND
• MINUTE
• MINUTE TO SECOND
• SECOND

示例

以下示例將當前日期加上3年。

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03' YEAR; 
  Date    (Date+ 3) 
--------  --------- 
16/01/01   19/01/01

以下示例將當前日期加上3年零1個月。

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03-01' YEAR TO MONTH; 
 Date     (Date+ 3-01) 
--------  ------------ 
16/01/01    19/02/01

以下示例將當前時間戳加上1天5小時10分鐘。

SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP + INTERVAL '01 05:10' DAY TO MINUTE; 
     Current TimeStamp(6)         (Current TimeStamp(6)+ 1 05:10) 
--------------------------------  -------------------------------- 
2016-01-01 04:57:26.360000+00:00  2016-01-02 10:07:26.360000+00:00

Teradata - 內建函式

Teradata提供內建函式，這些函式是SQL的擴充套件。以下是常見的內建函式。

Teradata - 聚合函式

Teradata支援常見的聚合函式。它們可以與SELECT語句一起使用。

• COUNT − 計數行數

• SUM − 將指定列的值加起來

• MAX − 返回指定列的最大值

• MIN − 返回指定列的最小值

• AVG − 返回指定列的平均值

示例

考慮以下薪資表。

COUNT

以下示例計算薪資表中記錄的數量。

SELECT count(*) from Salary;  

  Count(*) 
----------- 
    5 

MAX

以下示例返回員工淨薪的最大值。

SELECT max(NetPay) from Salary;   
   Maximum(NetPay) 
--------------------- 
       83000 

MIN

以下示例返回薪資表中員工淨薪的最小值。

SELECT min(NetPay) from Salary;   

   Minimum(NetPay) 
--------------------- 
        36000

AVG

以下示例返回表中員工淨薪的平均值。

SELECT avg(NetPay) from Salary; 
  
   Average(NetPay) 
--------------------- 
       65800 

SUM

以下示例計算薪資表中所有記錄的員工淨薪總和。

SELECT sum(NetPay) from Salary;
  
   Sum(NetPay) 
----------------- 
     329000

Teradata - CASE 和 COALESCE

本章解釋Teradata的CASE和COALESCE函式。

CASE表示式

CASE表示式根據條件或WHEN子句評估每一行，並返回第一個匹配項的結果。如果沒有匹配項，則返回ELSE部分的結果。

語法

以下是CASE表示式的語法。

CASE <expression> 
WHEN <expression> THEN result-1 
WHEN <expression> THEN result-2 

ELSE  
   Result-n 
END

示例

考慮以下員工表。

以下示例評估DepartmentNo列，如果部門編號為1，則返回1；如果部門編號為3，則返回2；否則返回無效部門。

SELECT 
   EmployeeNo, 
CASE DepartmentNo 
   WHEN 1 THEN 'Admin' 
   WHEN 2 THEN 'IT' 
ELSE 'Invalid Dept'
   END AS Department 
FROM Employee; 

執行上述查詢時，將產生以下輸出。

*** Query completed. 5 rows found. 2 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo    Department 
-----------   ------------ 
   101         Admin 
   104         IT 
   102         IT 
   105         Invalid Dept 
   103         IT

上述CASE表示式也可以寫成以下形式，這將產生與上述相同的結果。

SELECT 
   EmployeeNo, 
CASE  
   WHEN DepartmentNo = 1 THEN 'Admin' 
   WHEN  DepartmentNo = 2 THEN 'IT' 
ELSE 'Invalid Dept' 
   END AS Department  
FROM Employee;

COALESCE

COALESCE是一個語句，它返回表示式的第一個非空值。如果表示式的所有引數都計算為NULL，則返回NULL。以下是語法。

語法

COALESCE(expression 1, expression 2, ....) 

示例

SELECT 
   EmployeeNo, 
   COALESCE(dept_no, 'Department not found') 
FROM  
   employee;

NULLIF

如果引數相等，NULLIF語句返回NULL。

語法

以下是NULLIF語句的語法。

NULLIF(expression 1, expression 2) 

示例

如果DepartmentNo等於3，則以下示例返回NULL。否則，它返回DepartmentNo值。

SELECT 
   EmployeeNo,  
   NULLIF(DepartmentNo,3) AS department 
FROM Employee;

上述查詢返回以下記錄。您可以看到員工105的部門編號為NULL。

*** Query completed. 5 rows found. 2 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo      department 
-----------  ------------------ 
    101              1 
    104              2 
    102              2 
    105              ? 
    103              2

Teradata - 主索引

主鍵用於指定資料在Teradata中的位置。它用於指定哪個AMP獲取資料行。Teradata中的每個表都需要定義一個主鍵。如果沒有定義主鍵，Teradata會自動分配主鍵。主鍵提供了訪問資料的最快方式。主鍵最多可以有64列。

建立表時定義主鍵。主鍵有兩種型別。

• 唯一主鍵(UPI)
• 非唯一主鍵(NUPI)

唯一主鍵 (UPI)

如果表被定義為具有UPI，則被視為UPI的列不應有任何重複值。如果插入任何重複值，它們將被拒絕。

建立唯一主鍵

以下示例建立Salary表，其中EmployeeNo列作為唯一主鍵。

CREATE SET TABLE Salary ( 
   EmployeeNo INTEGER, 
   Gross INTEGER,  
   Deduction INTEGER, 
   NetPay INTEGER 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

非唯一主鍵 (NUPI)

如果表被定義為具有NUPI，則被視為UPI的列可以接受重複值。

建立非唯一主鍵

以下示例建立employee accounts表，其中EmployeeNo列作為非唯一主鍵。EmployeeNo被定義為非唯一主鍵，因為員工可以在表中擁有多個帳戶；一個用於工資帳戶，另一個用於報銷帳戶。

CREATE SET TABLE Employee _Accounts ( 
   EmployeeNo INTEGER, 
   employee_bank_account_type BYTEINT. 
   employee_bank_account_number INTEGER, 
   employee_bank_name VARCHAR(30), 
   employee_bank_city VARCHAR(30) 
) 
PRIMARY INDEX(EmployeeNo);

Teradata - 連線

連線用於組合來自多個表的記錄。表根據這些表的公共列/值進行連線。

有不同型別的連線可用。

• 內連線
• 左外連線
• 右外連線
• 全外連線
• 自連線
• 交叉連線
• 笛卡爾積連線

INNER JOIN

內連線組合來自多個表的記錄，並返回同時存在於兩個表中的值。

語法

以下是INNER JOIN語句的語法。

SELECT col1, col2, col3…. 
FROM  
Table-1 
INNER JOIN 
Table-2 
ON (col1 = col2) 
<WHERE condition>;

示例

考慮以下員工表和薪資表。

以下查詢根據公共列EmployeeNo連線Employee表和Salary表。每個表都被分配了一個別名A & B，並且列用正確的別名引用。

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay 
FROM  
Employee A 
INNER JOIN 
Salary B 
ON (A.EmployeeNo = B. EmployeeNo);

執行上述查詢時，它將返回以下記錄。員工105未包含在結果中，因為它在Salary表中沒有匹配的記錄。

*** Query completed. 4 rows found. 3 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo   DepartmentNo     NetPay 
-----------  ------------   ----------- 
    101           1            36000 
    102           2            74000 
    103           2            83000 
    104           2            70000

OUTER JOIN

LEFT OUTER JOIN和RIGHT OUTER JOIN也組合來自多個表的結果。

• LEFT OUTER JOIN返回左表的所有記錄，只返回右表中的匹配記錄。

• RIGHT OUTER JOIN返回右表的所有記錄，只返回左表中的匹配行。

• FULL OUTER JOIN組合LEFT OUTER JOIN和RIGHT OUTER JOIN的結果。它返回連線表中的匹配行和不匹配行。

語法

以下是OUTER JOIN語句的語法。您需要使用LEFT OUTER JOIN、RIGHT OUTER JOIN或FULL OUTER JOIN中的一個選項。

SELECT col1, col2, col3…. 
FROM  
Table-1 
LEFT OUTER JOIN/RIGHT OUTER JOIN/FULL OUTER JOIN 
Table-2 
ON (col1 = col2) 
<WHERE condition>;

示例

考慮以下LEFT OUTER JOIN查詢示例。它返回Employee表的所有記錄和Salary表中的匹配記錄。

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay 
FROM  
Employee A 
LEFT OUTER JOIN 
Salary B 
ON (A.EmployeeNo = B. EmployeeNo) 
ORDER BY A.EmployeeNo; 

執行上述查詢時，它將產生以下輸出。對於員工105，NetPay值為NULL，因為它在Salary表中沒有匹配的記錄。

*** Query completed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo  DepartmentNo     NetPay 
-----------  ------------   ----------- 
    101           1           36000 
    102           2           74000 
    103           2           83000 
    104           2           70000 
    105           3             ?

CROSS JOIN

交叉連線將左表中的每一行與右表中的每一行連線。

語法

以下是CROSS JOIN語句的語法。

SELECT A.EmployeeNo, A.DepartmentNo, B.EmployeeNo,B.NetPay 
FROM  
Employee A 
CROSS JOIN 
Salary B 
WHERE A.EmployeeNo = 101 
ORDER BY B.EmployeeNo;

執行上述查詢時，它將產生以下輸出。Employee表中的員工編號101與Salary表中的每個記錄連線。

*** Query completed. 4 rows found. 4 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo  DepartmentNo   EmployeeNo    NetPay 
-----------  ------------  -----------  ----------- 
    101           1            101         36000 
    101           1            104         70000 
    101           1            102         74000 
    101           1            103         83000

Teradata - 子查詢

子查詢根據另一個表中的值返回一個表中的記錄。它是在另一個查詢中的SELECT查詢。名為內部查詢的SELECT查詢首先執行，其結果由外部查詢使用。它的一些顯著特徵是：

• 一個查詢可以有多個子查詢，子查詢可能包含另一個子查詢。

• 子查詢不返回重複記錄。

• 如果子查詢只返回一個值，您可以使用=運算子將其與外部查詢一起使用。如果它返回多個值，您可以使用IN或NOT IN。

語法

以下是子查詢的通用語法。

SELECT col1, col2, col3,… 
FROM  
Outer Table 
WHERE col1 OPERATOR ( Inner SELECT Query);

示例

考慮以下薪資表。

以下查詢確定薪水最高的員工編號。內部SELECT執行聚合函式以返回最大NetPay值，外部SELECT查詢使用此值返回具有此值的員工記錄。

SELECT EmployeeNo, NetPay 
FROM Salary 
WHERE NetPay =  
(SELECT MAX(NetPay)  
FROM Salary);

執行此查詢時，它將產生以下輸出。

*** Query completed. One row found. 2 columns returned. 
*** Total elapsed time was 1 second.  
 EmployeeNo     NetPay 
-----------  ----------- 
    103         83000 

Teradata - 表型別

Teradata支援以下表型別來儲存臨時資料。

• 派生表
• 易失表
• 全域性臨時表

派生表

派生表在查詢中建立、使用和刪除。這些用於儲存查詢中的中間結果。

示例

以下示例構建一個派生表EmpSal，其中包含薪水大於75000的員工記錄。

SELECT 
Emp.EmployeeNo, 
Emp.FirstName, 
Empsal.NetPay 
FROM 
Employee Emp, 
(select EmployeeNo , NetPay 
from Salary
where NetPay >= 75000) Empsal 
where Emp.EmployeeNo = Empsal.EmployeeNo;

執行上述查詢時，它將返回薪水大於75000的員工。

*** Query completed. One row found. 3 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo            FirstName               NetPay 
-----------  ------------------------------  ----------- 
    103                  Peter                 83000 

易失表

易失表在使用者會話中建立、使用和刪除。它們的定義未儲存在資料字典中。它們儲存經常使用的查詢的中間資料。以下是語法。

語法

CREATE [SET|MULTISET] VOALTILE TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 
ON COMMIT [DELETE|PRESERVE] ROWS

示例

CREATE VOLATILE TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no) 
ON COMMIT PRESERVE ROWS;

執行上述查詢時，將產生以下輸出。

*** Table has been created. 
*** Total elapsed time was 1 second.

全域性臨時表

全域性臨時表的定義儲存在資料字典中，許多使用者/會話可以使用它們。但是，僅在會話期間保留載入到全域性臨時表中的資料。每個會話最多可以實現2000個全域性臨時表。以下是語法。

語法

CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 

示例

CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no);

執行上述查詢時，將產生以下輸出。

*** Table has been created. 
*** Total elapsed time was 1 second.

Teradata - 空間概念

Teradata中有三種類型的空間可用。

永久空間

永久空間是使用者/資料庫可用於儲存資料行的最大空間量。永久表、日誌、回退表和輔助索引子表使用永久空間。

資料庫/使用者不會預分配永久空間。它們只是定義為資料庫/使用者可以使用的最大空間量。永久空間量除以AMP的數量。每當每個AMP限制超過時，都會生成錯誤訊息。

緩衝池空間

緩衝池空間是未使用的永久空間，系統使用它來儲存SQL查詢的中間結果。沒有緩衝池空間的使用者無法執行任何查詢。

與永久空間類似，緩衝池空間定義了使用者可以使用的最大空間量。緩衝池空間除以AMP的數量。每當每個AMP限制超過時，使用者將收到緩衝池空間錯誤。

臨時空間

臨時空間是全域性臨時表使用的未使用的永久空間。臨時空間也除以AMP的數量。

Teradata - 次要索引

表只能包含一個主鍵。更常見的是，您會遇到表包含其他列的情況，使用這些列可以頻繁地訪問資料。Teradata將對這些查詢執行全表掃描。輔助索引解決了這個問題。

輔助索引是訪問資料的替代路徑。主鍵和輔助索引之間存在一些差異。

• 輔助索引不參與資料分發。

• 輔助索引值儲存在子表中。這些表在所有AMP中構建。

• 輔助索引是可選的。

• 它們可以在建立表期間或表建立後建立。

• 它們佔用額外的空間，因為它們構建子表，並且它們也需要維護，因為需要為每一行更新子表。

輔助索引有兩種型別：

• 唯一輔助索引 (USI)
• 非唯一輔助索引 (NUSI)

唯一輔助索引 (USI)

唯一輔助索引只允許為定義為USI的列提供唯一值。透過USI訪問行是兩AMP操作。

建立唯一輔助索引

以下示例在employee表的EmployeeNo列上建立USI。

CREATE UNIQUE INDEX(EmployeeNo) on employee;

非唯一輔助索引 (NUSI)

非唯一二級索引允許為定義為 NUSI 的列包含重複值。透過 NUSI 訪問行是全 AMP 操作。

建立非唯一二級索引

以下示例在員工表的 FirstName 列上建立 NUSI。

CREATE INDEX(FirstName) on Employee;

Teradata - 統計資訊

Teradata 最佳化器會為每個 SQL 查詢制定執行策略。此執行策略基於在 SQL 查詢中使用的表上收集的統計資訊。表上的統計資訊是使用 COLLECT STATISTICS 命令收集的。最佳化器需要環境資訊和資料特徵才能制定最佳執行策略。

環境資訊

• 節點、AMP 和 CPU 數量
• 記憶體大小

資料特徵

• 行數
• 行大小
• 表中值的範圍
• 每個值的行數
• 空值數量

有三種方法可以收集表上的統計資訊。

• 隨機 AMP 取樣
• 完整統計資訊收集
• 使用 SAMPLE 選項

收集統計資訊

COLLECT STATISTICS 命令用於收集表上的統計資訊。

語法

以下是收集表上統計資訊的的基本語法。

COLLECT [SUMMARY] STATISTICS   
INDEX (indexname) COLUMN (columnname) 
ON <tablename>;

示例

以下示例收集員工表 EmployeeNo 列的統計資訊。

COLLECT STATISTICS COLUMN(EmployeeNo) ON Employee;

執行上述查詢時，將產生以下輸出。

*** Update completed. 2 rows changed. 
*** Total elapsed time was 1 second.

檢視統計資訊

可以使用 HELP STATISTICS 命令檢視已收集的統計資訊。

語法

以下是檢視已收集統計資訊的語法。

HELP STATISTICS <tablename>; 

示例

以下是如何檢視員工表已收集統計資訊的示例。

HELP STATISTICS employee;

執行上述查詢時，會產生以下結果。

  Date       Time      Unique Values           Column Names 
--------   -------- -------------------- ----------------------- 
16/01/01   08:07:04         5                       * 
16/01/01   07:24:16         3                   DepartmentNo 
16/01/01   08:07:04         5                   EmployeeNo

Teradata - 壓縮

壓縮用於減少表使用的儲存空間。在 Teradata 中，壓縮最多可以壓縮 255 個不同的值，包括 NULL 值。由於儲存空間減少了，Teradata 可以在一個塊中儲存更多記錄。這將提高查詢響應時間，因為任何 I/O 操作都可以每塊處理更多行。可以使用 CREATE TABLE 命令在表建立時新增壓縮，也可以使用 ALTER TABLE 命令在表建立後新增壓縮。

限制

• 每列最多隻能壓縮 255 個值。
• 主鍵列不能壓縮。
• 易失表不能壓縮。

多值壓縮 (MVC)

下表壓縮了值為 1、2 和 3 的 DepatmentNo 欄位。當對列應用壓縮時，該列的值不會與行一起儲存。相反，這些值儲存在每個 AMP 的表頭中，並且僅將存在位新增到行中以指示該值。

CREATE SET TABLE employee ( 
   EmployeeNo integer, 
   FirstName CHAR(30), 
   LastName CHAR(30), 
   BirthDate DATE FORMAT 'YYYY-MM-DD-', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD-', 
   employee_gender CHAR(1), 
   DepartmentNo CHAR(02) COMPRESS(1,2,3) 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

當大型表中有一列具有有限的值時，可以使用多值壓縮。

Teradata - EXPLAIN

EXPLAIN 命令以英文返回解析引擎的執行計劃。它可以與任何 SQL 語句一起使用，但不能用於另一個 EXPLAIN 命令。當查詢前帶有 EXPLAIN 命令時，解析引擎的執行計劃將返回給使用者，而不是 AMP。

EXPLAIN 示例

考慮具有以下定義的 Employee 表。

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30), 
   LastName VARCHAR(30),
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

以下是一些 EXPLAIN 計劃示例。

全表掃描 (FTS)

如果 SELECT 語句中未指定任何條件，則最佳化器可能會選擇使用全表掃描，其中訪問表的每一行。

示例

以下是一個示例查詢，其中最佳化器可能會選擇 FTS。

EXPLAIN SELECT * FROM employee;

執行上述查詢時，會產生以下輸出。可以看出，最佳化器選擇訪問所有 AMP 和 AMP 中的所有行。

1) First, we lock a distinct TDUSER."pseudo table" for read on a 
   RowHash to prevent global deadlock for TDUSER.employee.  
2) Next, we lock TDUSER.employee for read.  
3) We do an all-AMPs RETRIEVE step from TDUSER.employee by way of an
   all-rows scan with no residual conditions into Spool 1 
   (group_amps), which is built locally on the AMPs.  The size of 
   Spool 1 is estimated with low confidence to be 2 rows (116 bytes).  
   The estimated time for this step is 0.03 seconds.  
4) Finally, we send out an END TRANSACTION step to all AMPs involved 
   in processing the request. 
→ The contents of Spool 1 are sent back to the user as the result of 
   statement 1.  The total estimated time is 0.03 seconds.

唯一主鍵索引

使用唯一主鍵索引訪問行時，這是一個 AMP 操作。

EXPLAIN SELECT * FROM employee WHERE EmployeeNo = 101;

執行上述查詢時，會產生以下輸出。可以看出，這是一個單 AMP 檢索，最佳化器正在使用唯一主鍵索引來訪問行。

1) First, we do a single-AMP RETRIEVE step from TDUSER.employee by 
   way of the unique primary index "TDUSER.employee.EmployeeNo = 101" 
   with no residual conditions. The estimated time for this step is 
   0.01 seconds.  
→ The row is sent directly back to the user as the result of 
   statement 1.  The total estimated time is 0.01 seconds.

唯一二級索引

使用唯一二級索引訪問行時，這是一個雙 AMP 操作。

示例

考慮具有以下定義的 Salary 表。

CREATE SET TABLE SALARY,FALLBACK ( 
   EmployeeNo INTEGER, 
   Gross INTEGER, 
   Deduction INTEGER, 
   NetPay INTEGER 
)
PRIMARY INDEX ( EmployeeNo ) 
UNIQUE INDEX (EmployeeNo);

考慮以下 SELECT 語句。

EXPLAIN SELECT * FROM Salary WHERE EmployeeNo = 101;

執行上述查詢時，會產生以下輸出。可以看出，最佳化器使用唯一二級索引在兩個 AMP 操作中檢索行。

1) First, we do a two-AMP RETRIEVE step from TDUSER.Salary 
   by way of unique index # 4 "TDUSER.Salary.EmployeeNo = 
   101" with no residual conditions.  The estimated time for this 
   step is 0.01 seconds.  
→ The row is sent directly back to the user as the result of 
   statement 1.  The total estimated time is 0.01 seconds.

附加術語

以下是 EXPLAIN 計劃中常見的術語列表。

…（最後使用）…

不再需要臨時檔案，此步驟完成後將釋放。

…沒有殘留條件…

所有適用的條件都已應用於行。

…END TRANSACTION…

事務鎖被釋放，更改被提交。

…消除重複行…

重複行僅存在於臨時檔案中，而不存在於集合表中。正在執行 DISTINCT 操作。

…透過遍歷索引 #n 僅提取行 ID 的方式…

構建一個包含在二級索引（索引 #n）中找到的行 ID 的臨時檔案。

…我們執行 SMS（集合操作步驟）…

使用 UNION、MINUS 或 INTERSECT 運算子組合行。

…它透過雜湊碼重新分佈到所有 AMP。

重新分佈資料以準備連線。

…它在所有 AMP 上覆制。

複製來自較小表（就 SPOOL 而言）的資料以準備連線。

…（one_AMP）或（group_AMPs）

指示將使用一個 AMP 或 AMP 子集，而不是所有 AMP。

Teradata - 雜湊演算法

行根據主鍵值分配給特定的 AMP。Teradata 使用雜湊演算法來確定哪個 AMP 獲取該行。

以下是關於雜湊演算法的高階圖表。

以下是插入資料的步驟。

• 客戶端提交查詢。

• 解析器接收查詢並將記錄的 PI 值傳遞給雜湊演算法。

• 雜湊演算法對主鍵值進行雜湊處理並返回一個 32 位數字，稱為行雜湊。

• 行雜湊的高位（前 16 位）用於標識雜湊對映條目。雜湊對映包含一個 AMP 編號。雜湊對映是一個桶陣列，包含特定的 AMP 編號。

• BYNET 將資料傳送到已識別的 AMP。

• AMP 使用 32 位行雜湊在其磁碟中定位行。

• 如果存在任何具有相同行雜湊的記錄，則它會遞增唯一性 ID（一個 32 位數字）。對於新的行雜湊，唯一性 ID 被分配為 1，並且每當插入具有相同行雜湊的記錄時都會遞增。

• 行雜湊和唯一性 ID 的組合稱為行 ID。

• 行 ID 在磁碟中為每條記錄新增字首。

• AMP 中的每個錶行按其行 ID 邏輯排序。

表的儲存方式

表按其行 ID（行雜湊 + 唯一性 ID）排序，然後儲存在 AMP 中。行 ID 與每條資料行一起儲存。

Teradata - JOIN 索引

JOIN 索引是物化檢視。其定義永久儲存，並且每當 JOIN 索引中引用的基表更新時，資料也會更新。JOIN 索引可能包含一個或多個表，也可能包含預聚合資料。JOIN 索引主要用於提高效能。

有不同型別的 JOIN 索引可用。

• 單表 JOIN 索引 (STJI)
• 多表 JOIN 索引 (MTJI)
• 聚合 JOIN 索引 (AJI)

單表 JOIN 索引

單表 JOIN 索引允許基於與基表不同的主鍵列對大型表進行分割槽。

語法

以下是 JOIN 索引的語法。

CREATE JOIN INDEX <index name> 
AS 
<SELECT Query> 
<Index Definition>;

示例

考慮以下 Employee 和 Salary 表。

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

CREATE SET TABLE SALARY,FALLBACK ( 
   EmployeeNo INTEGER, 
   Gross INTEGER, 
   Deduction INTEGER, 
   NetPay INTEGER 
) 
PRIMARY INDEX ( EmployeeNo ) 
UNIQUE INDEX (EmployeeNo);

以下示例建立名為 Employee_JI 的 Employee 表的 JOIN 索引。

CREATE JOIN INDEX Employee_JI 
AS 
SELECT EmployeeNo,FirstName,LastName, 
BirthDate,JoinedDate,DepartmentNo 
FROM Employee 
PRIMARY INDEX(FirstName);

如果使用者提交的查詢包含關於 EmployeeNo 的 WHERE 子句，則系統將使用唯一主鍵索引查詢 Employee 表。如果使用者使用 employee_name 查詢員工表，則系統可以使用 employee_name 訪問 JOIN 索引 Employee_JI。JOIN 索引的行在 employee_name 列上進行雜湊處理。如果沒有定義 JOIN 索引並且 employee_name 沒有定義為二級索引，則系統將執行全表掃描來訪問行，這將非常耗時。

您可以執行以下 EXPLAIN 計劃並驗證最佳化器計劃。在以下示例中，您可以看到當表使用 Employee_Name 列進行查詢時，最佳化器使用 JOIN 索引而不是基表 Employee。

EXPLAIN SELECT * FROM EMPLOYEE WHERE FirstName='Mike'; 
*** Help information returned. 8 rows. 
*** Total elapsed time was 1 second. 
Explanation 
------------------------------------------------------------------------ 
   1) First, we do a single-AMP RETRIEVE step from EMPLOYEE_JI by 
      way of the primary index "EMPLOYEE_JI.FirstName = 'Mike'" 
      with no residual conditions into Spool 1 (one-amp), which is built 
      locally on that AMP.  The size of Spool 1 is estimated with low 
      confidence to be 2 rows (232 bytes).  The estimated time for this 
      step is 0.02 seconds.
   → The contents of Spool 1 are sent back to the user as the result of 
      statement 1.  The total estimated time is 0.02 seconds. 

多表 JOIN 索引

多表 JOIN 索引是透過連線多個表建立的。多表 JOIN 索引可用於儲存經常連線的表的結果集以提高效能。

示例

以下示例透過連線 Employee 和 Salary 表建立名為 Employee_Salary_JI 的 JOIN 索引。

CREATE JOIN INDEX Employee_Salary_JI 
AS 
SELECT a.EmployeeNo,a.FirstName,a.LastName, 
a.BirthDate,a.JoinedDate,a.DepartmentNo,b.Gross,b.Deduction,b.NetPay 
FROM Employee a 
INNER JOIN Salary b 
ON(a.EmployeeNo = b.EmployeeNo) 
PRIMARY INDEX(FirstName);

每當基表 Employee 或 Salary 更新時，JOIN 索引 Employee_Salary_JI 也會自動更新。如果您正在執行連線 Employee 和 Salary 表的查詢，則最佳化器可能會選擇直接從 Employee_Salary_JI 訪問資料，而不是連線表。查詢的 EXPLAIN 計劃可用於驗證最佳化器是否會選擇基表或 JOIN 索引。

聚合 JOIN 索引

如果表始終在某些列上進行聚合，則可以在表上定義聚合 JOIN 索引以提高效能。聚合 JOIN 索引的一個限制是它僅支援 SUM 和 COUNT 函式。

示例

在以下示例中，Employee 和 Salary 連線以識別每個部門的總薪水。

CREATE JOIN INDEX Employee_Salary_JI 
AS 
SELECT a.DepartmentNo,SUM(b.NetPay) AS TotalPay 
FROM Employee a 
INNER JOIN Salary b 
ON(a.EmployeeNo = b.EmployeeNo)
GROUP BY a.DepartmentNo 
Primary Index(DepartmentNo); 

Teradata - 檢視

檢視是由查詢構建的資料庫物件。檢視可以使用單個表或透過連線使用多個表來構建。它們的定義永久儲存在資料字典中，但它們不儲存資料的副本。檢視的資料是動態構建的。

檢視可能包含表的行子集或表的列子集。

建立檢視

檢視是使用 CREATE VIEW 語句建立的。

語法

以下是建立檢視的語法。

CREATE/REPLACE VIEW <viewname> 
AS  
<select query>; 

示例

考慮以下員工表。

以下示例在 Employee 表上建立一個檢視。

CREATE VIEW Employee_View 
AS 
SELECT 
EmployeeNo, 
FirstName, 
LastName, 
FROM  
Employee;

使用檢視

您可以使用常規 SELECT 語句從檢視檢索資料。

示例

以下示例從 Employee_View 檢索記錄；

SELECT EmployeeNo, FirstName, LastName FROM Employee_View;

執行上述查詢時，將產生以下輸出。

*** Query completed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 second.  
 EmployeeNo            FirstName                       LastName 
-----------  ------------------------------  --------------------------- 
    101                  Mike                           James 
    104                  Alex                           Stuart 
    102                  Robert                         Williams 
    105                  Robert                         James 
    103                  Peter                          Paul 

修改檢視

可以使用 REPLACE VIEW 語句修改現有檢視。

以下是修改檢視的語法。

REPLACE VIEW <viewname> 
AS  
<select query>;

示例

以下示例修改 Employee_View 檢視以新增其他列。

REPLACE VIEW Employee_View 
AS 
SELECT 
EmployeeNo, 
FirstName, 
BirthDate,
JoinedDate 
DepartmentNo 
FROM  
Employee; 

刪除檢視

可以使用 DROP VIEW 語句刪除現有檢視。

語法

以下是 DROP VIEW 的語法。

DROP VIEW <viewname>; 

示例

以下是如何刪除 Employee_View 檢視的示例。

DROP VIEW Employee_View; 

檢視的優點

• 檢視透過限制表的行或列來提供額外的安全級別。

• 使用者可以只被授予對檢視的訪問許可權，而不是對基表的訪問許可權。

• 透過使用檢視預先連線多個表來簡化多個表的使用。

Teradata - 宏

宏是一組儲存的SQL語句，透過呼叫宏名稱來執行。宏的定義儲存在資料字典中。使用者只需要 EXEC 許可權即可執行宏。使用者不需要對宏中使用的資料庫物件擁有單獨的許可權。宏語句作為一個事務執行。如果宏中的一個SQL語句失敗，則所有語句都將回滾。宏可以接受引數。宏可以包含DDL語句，但這應該是宏中的最後一條語句。

建立宏

使用 CREATE MACRO 語句建立宏。

語法

以下是 CREATE MACRO 命令的通用語法。

CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( 
   <sql statements> 
);

示例

考慮以下員工表。

以下示例建立一個名為 Get_Emp 的宏。它包含一個 select 語句，用於從員工表檢索記錄。

CREATE MACRO Get_Emp AS ( 
   SELECT 
   EmployeeNo, 
   FirstName, 
   LastName 
   FROM  
   employee 
   ORDER BY EmployeeNo; 
);

執行宏

使用 EXEC 命令執行宏。

語法

以下是 EXECUTE MACRO 命令的語法。

EXEC <macroname>; 

示例

以下示例執行名為 Get_Emp 的宏；執行以下命令時，它將檢索員工表中的所有記錄。

EXEC Get_Emp; 
*** Query completed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 second.  
EmployeeNo             FirstName                      LastName 
-----------  ------------------------------  --------------------------- 
   101                  Mike                          James 
   102                  Robert                        Williams 
   103                  Peter                         Paul 
   104                  Alex                          Stuart 
   105                  Robert                        James 

引數化宏

Teradata 宏可以接受引數。在宏中，這些引數用 ;（分號）引用。

以下是一個接受引數的宏示例。

CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( 
   SELECT 
   EmployeeNo, 
   NetPay 
   FROM  
   Salary 
   WHERE EmployeeNo = :EmployeeNo; 
);

執行引數化宏

使用 EXEC 命令執行宏。您需要 EXEC 許可權才能執行宏。

語法

以下是 EXECUTE MACRO 語句的語法。

EXEC <macroname>(value);

示例

以下示例執行名為 Get_Emp 的宏；它接受員工編號作為引數，並提取該員工的員工表記錄。

EXEC Get_Emp_Salary(101); 
*** Query completed. One row found. 2 columns returned. 
*** Total elapsed time was 1 second.
 
EmployeeNo      NetPay 
-----------  ------------ 
   101           36000 

Teradata - 儲存過程

儲存過程包含一組SQL語句和過程語句。它們可能只包含過程語句。儲存過程的定義儲存在資料庫中，引數儲存在資料字典表中。

優點

• 儲存過程減少了客戶端和伺服器之間的網路負載。

• 提供更好的安全性，因為資料是透過儲存過程訪問的，而不是直接訪問。

• 提供更好的維護，因為業務邏輯已在伺服器上進行測試和儲存。

建立過程

使用 CREATE PROCEDURE 語句建立儲存過程。

語法

以下是 CREATE PROCEDURE 語句的通用語法。

CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) 
BEGIN 
   <SQL or SPL statements>; 
END;

示例

考慮以下薪資表。

以下示例建立一個名為 InsertSalary 的儲存過程，用於接受值並插入到 Salary 表中。

CREATE PROCEDURE InsertSalary ( 
   IN in_EmployeeNo INTEGER, IN in_Gross INTEGER, 
   IN in_Deduction INTEGER, IN in_NetPay INTEGER 
) 
BEGIN 
   INSERT INTO Salary ( 
      EmployeeNo, 
      Gross, 
      Deduction, 
      NetPay 
   ) 
   VALUES ( 
      :in_EmployeeNo, 
      :in_Gross, 
      :in_Deduction, 
      :in_NetPay 
   ); 
END;

執行過程

使用 CALL 語句執行儲存過程。

語法

以下是 CALL 語句的通用語法。

CALL <procedure name> [(parameter values)];

示例

以下示例呼叫儲存過程 InsertSalary 並將記錄插入到 Salary 表中。

CALL InsertSalary(105,20000,2000,18000);

執行上述查詢後，它將產生以下輸出，您可以在 Salary 表中看到插入的行。

Teradata - 連線策略

本章討論 Teradata 中可用的各種連線策略。

連線方法

Teradata 使用不同的連線方法執行連線操作。一些常用的連線方法是：

• 合併連線
• 巢狀連線
• 笛卡爾積連線

合併連線

當連線基於相等條件時，將發生合併連線。合併連線要求連線行位於相同的 AMP 上。行根據其行雜湊進行連線。合併連線使用不同的連線策略將行帶到相同的 AMP。

策略 #1

如果連線列是相應表的主索引，則連線行已位於相同的 AMP 上。在這種情況下，不需要重新分佈。

考慮以下員工和薪資表。

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

CREATE SET TABLE Salary ( 
   EmployeeNo INTEGER, 
   Gross INTEGER,  
   Deduction INTEGER, 
   NetPay INTEGER 
) 
UNIQUE PRIMARY INDEX(EmployeeNo); 

當這兩個表根據 EmployeeNo 列連線時，不會發生重新分佈，因為 EmployeeNo 是正在連線的兩個表的主索引。

策略 #2

考慮以下員工和部門表。

CREATE SET TABLE EMPLOYEE,FALLBACK (
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

CREATE SET TABLE DEPARTMENT,FALLBACK ( 
   DepartmentNo BYTEINT, 
   DepartmentName CHAR(15) 
) 
UNIQUE PRIMARY INDEX ( DepartmentNo );

如果這兩個表根據 DeparmentNo 列連線，則需要重新分佈行，因為 DepartmentNo 在一個表中是主索引，而在另一個表中是非主索引。在這種情況下，連線行可能不在相同的 AMP 上。在這種情況下，Teradata 可能會根據 DepartmentNo 列重新分佈員工表。

策略 #3

對於上述員工和部門表，如果部門表的大小較小，Teradata 可能會在所有 AMP 上覆制部門表。

巢狀連線

巢狀連線不使用所有 AMP。為了進行巢狀連線，其中一個條件應該是對一個表唯一主鍵的相等條件，然後將此列連線到另一個表的任何索引。

在這種情況下，系統將使用一個表唯一主鍵獲取一行，並使用該行雜湊從另一個表中獲取匹配的記錄。巢狀連線是所有連線方法中最有效的。

笛卡爾積連線

笛卡爾積連線將一個表中的每個合格行與另一個表中的每個合格行進行比較。由於以下一些因素，可能會發生笛卡爾積連線：

• 缺少 where 條件。
• 連線條件不是基於相等條件。
• 表別名不正確。
• 多個連線條件。

Teradata - 分割槽主索引

分割槽主鍵索引 (PPI) 是一種索引機制，可用於提高某些查詢的效能。當將行插入表中時，它們將儲存在 AMP 中並按其行雜湊順序排列。當表使用 PPI 定義時，行按其分割槽號排序。在每個分割槽內，它們按其行雜湊排列。行根據定義的分割槽表示式分配給分割槽。

優點

• 避免對某些查詢進行全表掃描。

• 避免使用需要額外物理結構和額外 I/O 維護的輔助索引。

• 快速訪問大型表的子集。

• 快速刪除舊資料並新增新資料。

示例

考慮以下具有 OrderNo 主索引的 Orders 表。

假設記錄在 AMP 之間分佈，如以下表所示。記錄儲存在 AMP 中，並根據其行雜湊排序。

AMP 1

AMP 2

如果您執行查詢以提取特定日期的訂單，則最佳化器可能會選擇使用全表掃描，然後可能會訪問 AMP 中的所有記錄。為了避免這種情況，您可以將訂單日期定義為分割槽主鍵索引。當行插入訂單表時，它們將按訂單日期進行分割槽。在每個分割槽內，它們將按其行雜湊排序。

以下資料顯示瞭如果按訂單日期進行分割槽，記錄將如何儲存在 AMP 中。如果執行查詢以按訂單日期訪問記錄，則只訪問包含該特定訂單記錄的分割槽。

AMP 1

AMP 2

以下是如何建立具有分割槽主鍵索引的表的示例。PARTITION BY 子句用於定義分割槽。

CREATE SET TABLE Orders (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE FORMAT 'YYYY-MM-DD', 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY
);

在上面的示例中，表按 OrderDate 列進行分割槽。每天將有一個單獨的分割槽。

Teradata - OLAP 函式

OLAP 函式類似於聚合函式，除了聚合函式只返回一個值，而 OLAP 函式除了聚合之外還會提供各個行。

語法

以下是 OLAP 函式的通用語法。

<aggregate function> OVER  
([PARTITION BY] [ORDER BY columnname][ROWS BETWEEN 
UNBOUDED PRECEDING AND UNBOUNDED FOLLOWING) 

聚合函式可以是 SUM、COUNT、MAX、MIN、AVG。

示例

考慮以下薪資表。

以下是如何查詢 Salary 表上 NetPay 的累積和或執行總計的示例。記錄按 EmployeeNo 排序，並計算 NetPay 列的累積和。

SELECT  
EmployeeNo, NetPay, 
SUM(Netpay) OVER(ORDER BY EmployeeNo ROWS  
UNBOUNDED PRECEDING) as TotalSalary 
FROM Salary;

執行上述查詢時，將產生以下輸出。

EmployeeNo     NetPay     TotalSalary 
-----------  -----------  ----------- 
   101         36000        36000 
   102         74000        110000 
   103         83000        193000 
   104         70000        263000 
   105         18000        281000 

RANK

RANK 函式根據提供的列對記錄進行排序。RANK 函式還可以根據排名過濾返回的記錄數。

語法

以下是使用 RANK 函式的通用語法。

RANK() OVER 
([PARTITION BY columnnlist] [ORDER BY columnlist][DESC|ASC])

示例

考慮以下員工表。

以下查詢按加入日期對員工表記錄進行排序，並根據加入日期分配排名。

SELECT EmployeeNo, JoinedDate,RANK() 
OVER(ORDER BY JoinedDate) as Seniority 
FROM Employee;

執行上述查詢時，將產生以下輸出。

EmployeeNo   JoinedDate   Seniority 
-----------  ----------  ----------- 
   101       2005-03-27       1 
   103       2007-03-21       2 
   102       2007-04-25       3 
   105       2008-01-04       4 
   104       2008-02-01       5 

PARTITION BY 子句按 PARTITION BY 子句中定義的列對資料進行分組，並在每個組內執行 OLAP 函式。以下是用 PARTITION BY 子句的查詢示例。

SELECT EmployeeNo, JoinedDate,RANK() 
OVER(PARTITION BY DeparmentNo ORDER BY JoinedDate) as Seniority 
FROM Employee;

執行上述查詢後，它將產生以下輸出。您可以看到每個部門的排名都會重置。

EmployeeNo  DepartmentNo  JoinedDate   Seniority 
-----------  ------------  ----------  ----------- 

    101           1        2005-03-27       1 
    103           2        2007-03-21       1 
    102           2        2007-04-25       2 
    104           2        2008-02-01       3 
    105           3        2008-01-04       1 

Teradata - 資料保護

本章討論 Teradata 中可用的資料保護功能。

瞬態日誌

Teradata 使用瞬態日誌來保護資料免受事務失敗的影響。每當執行任何事務時，瞬態日誌都會保留受影響行的之前映像的副本，直到事務成功或成功回滾。然後，將丟棄之前映像。瞬態日誌儲存在每個 AMP 中。這是一個自動過程，無法停用。

回退

回退透過將表的行副本儲存在另一個稱為回退 AMP 的 AMP 上來保護表資料。如果一個 AMP 失敗，則訪問回退行。這樣，即使一個 AMP 失敗，資料仍然可以透過回退 AMP 訪問。回退選項可以在建立表時或建立表後使用。回退確保表的行副本始終儲存在另一個 AMP 中，以保護資料免受 AMP 故障的影響。但是，回退會佔用插入/刪除/更新的兩倍儲存空間和 I/O。

下圖顯示瞭如何在另一個 AMP 中儲存行的回退副本。

宕機 AMP 恢復日誌

當 AMP 失敗並且表受到回退保護時，將啟用宕機 AMP 恢復日誌。此日誌跟蹤對失敗 AMP 資料的所有更改。該日誌在叢集中的其餘 AMP 上啟用。這是一個自動過程，無法停用。一旦失敗的 AMP 恢復執行，則宕機 AMP 恢復日誌中的資料將與 AMP 同步。完成後，將丟棄該日誌。

叢集

叢集是 Teradata 用於保護資料免受節點故障影響的機制。叢集只不過是一組共享常用磁碟陣列的 Teradata 節點。當一個節點發生故障時，來自失敗節點的 vproc 將遷移到叢集中的其他節點，並繼續訪問其磁碟陣列。

熱備節點

熱備節點是不參與生產環境的節點。如果一個節點發生故障，則來自失敗節點的 vproc 將遷移到熱備節點。一旦失敗的節點恢復，它將成為熱備節點。熱備節點用於在節點發生故障時保持效能。

RAID

獨立磁碟冗餘陣列 (RAID) 是一種用於保護資料免受磁碟故障影響的機制。磁碟陣列由一組磁碟組成，這些磁碟組合成一個邏輯單元。對於使用者而言，此單元可能看起來像單個單元，但它們可能分佈在多個磁碟上。

RAID 1 通常用於 Teradata。在 RAID 1 中，每個磁碟都與一個映象磁碟關聯。對主磁碟中資料的任何更改也都會反映在映象副本中。如果主磁碟發生故障，則可以訪問映象磁碟中的資料。

Teradata - 使用者管理

本章討論了 Teradata 中使用者管理的各種策略。

使用者

使用 CREATE USER 命令建立使用者。在 Teradata 中，使用者也類似於資料庫。兩者都可以分配空間幷包含資料庫物件，只是使用者分配了密碼。

語法

以下是 CREATE USER 的語法。

CREATE USER username 
AS  
[PERMANENT|PERM] = n BYTES 
PASSWORD = password 
TEMPORARY = n BYTES 
SPOOL = n BYTES;

建立使用者時，使用者名稱、永久空間和密碼的值是必需的。其他欄位是可選的。

示例

以下是如何建立使用者 TD01 的示例。

CREATE USER TD01 
AS  
PERMANENT = 1000000 BYTES 
PASSWORD = ABC$124 
TEMPORARY = 1000000 BYTES 
SPOOL = 1000000 BYTES;

帳戶

建立新使用者時，可以將使用者分配給帳戶。CREATE USER 中的 ACCOUNT 選項用於分配帳戶。使用者可以分配到多個帳戶。

語法

以下是帶有帳戶選項的 CREATE USER 的語法。

CREATE USER username 
PERM = n BYTES 
PASSWORD = password 
ACCOUNT = accountid

示例

以下示例建立使用者 TD02 並將其帳戶分配為 IT 和 Admin。

CREATE USER TD02 
AS  
PERMANENT = 1000000 BYTES 
PASSWORD = abc$123 
TEMPORARY = 1000000 BYTES 
SPOOL = 1000000 BYTES 
ACCOUNT = (‘IT’,’Admin’);

使用者可以在登入 Teradata 系統時或使用 SET SESSION 命令登入系統後指定帳戶 ID。

.LOGON username, passowrd,accountid 
OR 
SET SESSION ACCOUNT = accountid 

授予許可權

GRANT 命令用於將一個或多個數據庫物件的許可權賦予使用者或資料庫。

語法

以下是 GRANT 命令的語法。

GRANT privileges ON objectname TO username;

許可權可以是 INSERT、SELECT、UPDATE、REFERENCES。

示例

以下是一個 GRANT 語句的示例。

GRANT SELECT,INSERT,UPDATE ON Employee TO TD01;

撤銷許可權

REVOKE 命令用於從使用者或資料庫中刪除許可權。REVOKE 命令只能刪除顯式許可權。

語法

以下是 REVOKE 命令的基本語法。

REVOKE [ALL|privileges] ON objectname FROM username;

示例

以下是一個 REVOKE 命令的示例。

REVOKE INSERT,SELECT ON Employee FROM TD01;

Teradata - 效能調優

本章討論 Teradata 中效能調優的過程。

解釋

效能調優的第一步是對查詢使用 EXPLAIN。EXPLAIN 計劃詳細說明了最佳化器如何執行查詢。在 Explain 計劃中，檢查諸如置信度級別、使用的連線策略、臨時檔案大小、重新分配等關鍵字。

收集統計資訊

最佳化器使用資料統計資訊來制定有效的執行策略。COLLECT STATISTICS 命令用於收集表的統計資訊。確保收集的列統計資訊是最新的。

• 收集 WHERE 子句中使用的列以及連線條件中使用的列的統計資訊。

• 收集唯一主鍵列的統計資訊。

• 收集非唯一二級索引列的統計資訊。最佳化器將決定是否可以使用 NUSI 或全表掃描。

• 儘管收集了基表的統計資訊，但也要收集連線索引的統計資訊。

• 收集分割槽列的統計資訊。

資料型別

確保使用了正確的資料型別。這將避免使用超過所需的空間。

轉換

確保連線條件中使用的列的資料型別相容，以避免顯式資料轉換。

排序

除非需要，否則刪除不必要的 ORDER BY 子句。

臨時空間問題

如果查詢超過該使用者的每個 AMP 臨時空間限制，則會生成臨時空間錯誤。驗證解釋計劃並識別消耗更多臨時空間的步驟。這些中間查詢可以拆分並單獨放置以構建臨時表。

主鍵

確保為表正確定義了主鍵。主鍵列應均勻分佈資料，並且應經常用於訪問資料。

SET 表

如果定義 SET 表，則最佳化器將檢查為每個插入的記錄是否重複。要刪除重複檢查條件，可以為表定義唯一二級索引。

更新大型表

更新大型表將非常耗時。與其更新表，不如刪除記錄並插入包含修改行的記錄。

刪除臨時表

如果不再需要，請刪除臨時表（暫存表）和易失表。這將釋放永久空間和臨時空間。

MULTISET 表

如果您確定輸入記錄不會有重複記錄，則可以將目標表定義為 MULTISET 表，以避免 SET 表使用的重複行檢查。

Teradata - FastLoad

FastLoad 實用程式用於將資料載入到空表中。因為它不使用暫存日誌，所以可以快速載入資料。即使目標表是 MULTISET 表，它也不會載入重複行。

限制

目標表不應具有二級索引、連線索引和外部索引鍵引用。

FastLoad 的工作原理

FastLoad 分兩個階段執行。

階段 1

• 解析引擎從輸入檔案中讀取記錄並將一個塊傳送到每個 AMP。

• 每個 AMP 儲存記錄塊。

• 然後，AMP 對每個記錄進行雜湊處理並將其重新分配到正確的 AMP。

• 在階段 1 結束時，每個 AMP 都有其行，但它們並非按行雜湊順序排列。

階段 2

• 當 FastLoad 接收到 END LOADING 語句時，階段 2 開始。

• 每個 AMP 對記錄進行行雜湊排序並將其寫入磁碟。

• 目標表的鎖被釋放，錯誤表被刪除。

示例

建立一個包含以下記錄的文字檔案，並將檔案命名為 employee.txt。

101,Mike,James,1980-01-05,2010-03-01,1  
102,Robert,Williams,1983-03-05,2010-09-01,1 
103,Peter,Paul,1983-04-01,2009-02-12,2 
104,Alex,Stuart,1984-11-06,2014-01-01,2 
105,Robert,James,1984-12-01,2015-03-09,3

以下是一個將上述檔案載入到 Employee_Stg 表的 FastLoad 指令碼示例。

LOGON 192.168.1.102/dbc,dbc;  
   DATABASE tduser;  
   BEGIN LOADING tduser.Employee_Stg  
      ERRORFILES Employee_ET, Employee_UV  
      CHECKPOINT 10;  
      SET RECORD VARTEXT ",";  
      DEFINE in_EmployeeNo (VARCHAR(10)), 
         in_FirstName (VARCHAR(30)), 
         in_LastName (VARCHAR(30)), 
         in_BirthDate (VARCHAR(10)), 
         in_JoinedDate (VARCHAR(10)), 
         in_DepartmentNo (VARCHAR(02)), 
         FILE = employee.txt;
      INSERT INTO Employee_Stg (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate, 
         DepartmentNo
      ) 
      VALUES (  
         :in_EmployeeNo, 
         :in_FirstName, 
         :in_LastName, 
         :in_BirthDate (FORMAT 'YYYY-MM-DD'), 
         :in_JoinedDate (FORMAT 'YYYY-MM-DD'),
         :in_DepartmentNo
      ); 
   END LOADING;  
LOGOFF;

執行 FastLoad 指令碼

建立輸入檔案 employee.txt 並將 FastLoad 指令碼命名為 EmployeeLoad.fl 後，可以使用以下命令在 UNIX 和 Windows 中執行 FastLoad 指令碼。

FastLoad < EmployeeLoad.fl;

執行上述命令後，FastLoad 指令碼將執行並生成日誌。在日誌中，您可以看到 FastLoad 處理的記錄數量和狀態程式碼。

**** 03:19:14 END LOADING COMPLETE 
   Total Records Read              =  5 
   Total Error Table 1             =  0  ---- Table has been dropped 
   Total Error Table 2             =  0  ---- Table has been dropped 
   Total Inserts Applied           =  5 
   Total Duplicate Rows            =  0 
   Start:   Fri Jan  8 03:19:13 2016 
   End  :   Fri Jan  8 03:19:14 2016 
**** 03:19:14 Application Phase statistics: 
              Elapsed time: 00:00:01 (in hh:mm:ss) 
0008  LOGOFF; 
**** 03:19:15 Logging off all sessions

FastLoad 術語

以下是 FastLoad 指令碼中常用的術語列表。

• LOGON − 登入 Teradata 並啟動一個或多個會話。

• DATABASE − 設定預設資料庫。

• BEGIN LOADING − 標識要載入的表。

• ERRORFILES − 標識需要建立/更新的 2 個錯誤表。

• CHECKPOINT − 定義何時進行檢查點。

• SET RECORD − 指定輸入檔案格式是格式化、二進位制、文字還是非格式化。

• DEFINE − 定義輸入檔案佈局。

• FILE − 指定輸入檔名和路徑。

• INSERT − 將輸入檔案中的記錄插入目標表。

• END LOADING − 啟動 FastLoad 的階段 2。將記錄分配到目標表。

• LOGOFF − 結束所有會話並終止 FastLoad。

Teradata - MultiLoad

MultiLoad 可以在一次載入多個表，並且還可以執行不同的任務，例如 INSERT、DELETE、UPDATE 和 UPSERT。它一次最多可以載入 5 個表，並在指令碼中執行多達 20 個 DML 操作。MultiLoad 不需要目標表。

MultiLoad 支援兩種模式：

• 匯入
• 刪除

除了目標表外，MultiLoad 還需要一個工作表、一個日誌表和兩個錯誤表。

• 日誌表 − 用於維護載入過程中進行的檢查點，這些檢查點將用於重新啟動。

• 錯誤表 − 發生錯誤時，這些表在載入過程中被插入。第一個錯誤表儲存轉換錯誤，而第二個錯誤表儲存重複記錄。

• 日誌表 − 維持 MultiLoad 各階段的結果，用於重新啟動。

• 工作表 − MultiLoad 指令碼為每個目標表建立一個工作表。工作表用於儲存 DML 任務和輸入資料。

限制

MultiLoad 有一些限制。

• 目標表不支援唯一二級索引。
• 不支援引用完整性。
• 不支援觸發器。

MultiLoad 的工作原理

MultiLoad 匯入有五個階段：

• 階段 1 − 預備階段 – 執行基本的設定活動。

• 階段 2 − DML 事務階段 – 驗證 DML 語句的語法並將它們帶入 Teradata 系統。

• 階段 3 − 獲取階段 – 將輸入資料帶入工作表並鎖定表。

• 階段 4 − 應用階段 – 應用所有 DML 操作。

• 階段 5 − 清理階段 – 釋放表鎖。

MultiLoad 指令碼涉及的步驟：

• 步驟 1 − 設定日誌表。

• 步驟 2 − 登入 Teradata。

• 步驟 3 − 指定目標表、工作表和錯誤表。

• 步驟 4 − 定義輸入檔案佈局。

• 步驟 5 − 定義 DML 查詢。

• 步驟 6 − 命名匯入檔案。

• 步驟 7 − 指定要使用的佈局。

• 步驟 8 − 啟動載入。

• 步驟 9 − 完成載入並終止會話。

示例

建立一個包含以下記錄的文字檔案，並將檔案命名為 employee.txt。

101,Mike,James,1980-01-05,2010-03-01,1  
102,Robert,Williams,1983-03-05,2010-09-01,1 
103,Peter,Paul,1983-04-01,2009-02-12,2 
104,Alex,Stuart,1984-11-06,2014-01-01,2 
105,Robert,James,1984-12-01,2015-03-09,3 

以下示例是一個 MultiLoad 指令碼，它從 employee 表讀取記錄並載入到 Employee_Stg 表中。

.LOGTABLE tduser.Employee_log;  
.LOGON 192.168.1.102/dbc,dbc; 
   .BEGIN MLOAD TABLES Employee_Stg;  
      .LAYOUT Employee;  
      .FIELD in_EmployeeNo * VARCHAR(10);  
      .FIELD in_FirstName * VARCHAR(30); 
      .FIELD in_LastName * VARCHAR(30);  
      .FIELD in_BirthDate * VARCHAR(10); 
      .FIELD in_JoinedDate * VARCHAR(10);  
      .FIELD in_DepartmentNo * VARCHAR(02);

      .DML LABEL EmpLabel; 
      INSERT INTO Employee_Stg (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate,
         DepartmentNo
      )  
      VALUES (
         :in_EmployeeNo,
         :in_FirstName,
         :in_Lastname,
         :in_BirthDate,
         :in_JoinedDate,
         :in_DepartmentNo
      );
      .IMPORT INFILE employee.txt  
      FORMAT VARTEXT ','
      LAYOUT Employee
      APPLY EmpLabel;  
   .END MLOAD;  
LOGOFF;

執行 MultiLoad 指令碼

建立輸入檔案 employee.txt 並將 multiload 指令碼命名為 EmployeeLoad.ml 後，可以使用以下命令在 UNIX 和 Windows 中執行 Multiload 指令碼。

Multiload < EmployeeLoad.ml;

Teradata - FastExport

FastExport 實用程式用於將 Teradata 表中的資料匯出到平面檔案中。它還可以生成報表格式的資料。可以使用連線從一個或多個表中提取資料。由於 FastExport 以 64K 塊匯出資料，因此它對於提取大量資料非常有用。

示例

考慮以下員工表。

以下是一個 FastExport 指令碼示例。它匯出 employee 表中的資料並寫入 employeedata.txt 檔案。

.LOGTABLE tduser.employee_log;  
.LOGON 192.168.1.102/dbc,dbc;  
   DATABASE tduser;  
   .BEGIN EXPORT SESSIONS 2;  
      .EXPORT OUTFILE employeedata.txt  
      MODE RECORD FORMAT TEXT;
      SELECT CAST(EmployeeNo AS CHAR(10)), 
         CAST(FirstName AS CHAR(15)), 
         CAST(LastName AS CHAR(15)), 
         CAST(BirthDate AS CHAR(10))   
      FROM
      Employee;
   .END EXPORT;
.LOGOFF;

執行 FastExport 指令碼

編寫指令碼並將其命名為 employee.fx 後，可以使用以下命令執行指令碼。

fexp < employee.fx

執行上述命令後，您將在 employeedata.txt 檔案中收到以下輸出。

103       Peter          Paul           1983-04-01 
101       Mike           James          1980-01-05 
102       Robert         Williams       1983-03-05 
105       Robert         James          1984-12-01 
104       Alex           Stuart         1984-11-06

FastExport 術語

以下是 FastExport 指令碼中常用的術語列表。

• LOGTABLE − 指定用於重新啟動的日誌表。

• LOGON − 登入 Teradata 並啟動一個或多個會話。

• DATABASE − 設定預設資料庫。

• BEGIN EXPORT − 指示匯出的開始。

• EXPORT − 指定目標檔案和匯出格式。

• SELECT − 指定匯出資料的 select 查詢。

• END EXPORT − 指定 FastExport 的結束。

• LOGOFF − 結束所有會話並終止 FastExport。

Teradata - BTEQ

BTEQ 實用程式是 Teradata 中一個功能強大的實用程式，可在批處理模式和互動模式下使用。它可用於執行任何 DDL 語句、DML 語句、建立宏和儲存過程。BTEQ 可用於將資料從平面檔案匯入 Teradata 表，也可用於將資料從表提取到檔案或報表中。

BTEQ 術語

以下是 BTEQ 指令碼中常用的術語列表。

• LOGON − 用於登入 Teradata 系統。

• ACTIVITYCOUNT − 返回前一個查詢影響的行數。

• ERRORCODE − 返回前一個查詢的狀態程式碼。

• DATABASE − 設定預設資料庫。

• LABEL − 將標籤分配給一組 SQL 命令。

• RUN FILE − 執行檔案中包含的查詢。

• GOTO − 將控制轉移到標籤。

• LOGOFF − 從資料庫登出並終止所有會話。

• IMPORT − 指定輸入檔案路徑。

• EXPORT − 指定輸出檔案路徑並啟動匯出。

示例

以下是一個 BTEQ 指令碼示例。

.LOGON 192.168.1.102/dbc,dbc; 
   DATABASE tduser;

   CREATE TABLE employee_bkup ( 
      EmployeeNo INTEGER, 
      FirstName CHAR(30), 
      LastName CHAR(30), 
      DepartmentNo SMALLINT, 
      NetPay INTEGER 
   )
   Unique Primary Index(EmployeeNo);

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;
  
   SELECT * FROM  
   Employee 
   Sample 1; 
   .IF ACTIVITYCOUNT <> 0 THEN .GOTO InsertEmployee;  

   DROP TABLE employee_bkup;
  
   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
 
   .LABEL InsertEmployee 
   INSERT INTO employee_bkup 
   SELECT a.EmployeeNo, 
      a.FirstName, 
      a.LastName, 
      a.DepartmentNo, 
      b.NetPay 
   FROM  
   Employee a INNER JOIN Salary b 
   ON (a.EmployeeNo = b.EmployeeNo);  

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
.LOGOFF; 

上述指令碼執行以下任務：

• 登入 Teradata 系統。

• 設定預設資料庫。

• 建立一個名為 employee_bkup 的表。

• 從 Employee 表中選擇一條記錄以檢查表中是否有任何記錄。

• 如果表為空，則刪除 employee_bkup 表。

• 將控制轉移到標籤 InsertEmployee，該標籤將記錄插入 employee_bkup 表。

• 檢查 ERRORCODE 以確保語句成功，每個 SQL 語句之後都執行此操作。

• ACTIVITYCOUNT 返回前一個 SQL 查詢選擇/影響的記錄數。