Teradata 查詢指令 (7)

基本介紹

教學目標

初步了解 Teradata 中建立資料表的設計方式。

重點概念

在 Teradata 中會透過 AMP (Access Module Processors) 管理 Teradata 資料庫與磁碟系統之間的互動,主要透過雜湊將所有資料隨機平均的分散至所有 AMP 中,我們則可以在建立資料表之前以更有效率的設計平均分散資料,主要可以分為有主索引和沒有主索引兩大類。

資料表中的主索引 (Primary Index) 相當於關聯式資料模型中的主鍵值 (Primary Key),主要差異在於主索引的建立是為了更有效率的存取資料表中的資料。

有主索引 (Primary Index)

大部份的資料表主要會針對每列資料進行有主索引的資料表設計,這些資料表將會透過一致且簡單的雜湊公式分散資料在 AMP 主要會以主索引進行列的排序,當使用者以主索引查詢資料時將會直接將結果回傳。

建立不重複主索引資料表

1
2
3
4
5
6
7
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) UNIQUE PRIMARY INDEX(PRODUCT_NBR);

建立不重複多重主索引資料表

1
2
3
4
5
6
7
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) UNIQUE PRIMARY INDEX(TXN_DATE,PRODUCT_NBR);

在 Teradata 中更允許資料表建立分區主索引,PPI (Partition Primary Index) 資料表仍然會有主索引分散至 AMP 上,但是每個 AMP 排序資料在分區欄位中。取得結果時所有 AMP 將會包含其中,但是每個 AMP 只有讀取一個或多個相關分區,並不會進行完整資料表掃描。

建立分區主索引資料表 (By RANGE)

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PRIMARY INDEX(PRODUCT_NBR)
PARTITION BY RANGE_N (
TXN_DATE BETWEEN DATE '2015-01-01' AND DATE '2015-12-31'
EACH INTERVAL '1' MONTH) ;

建立分區主索引資料表 (By CASE)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PRIMARY INDEX(PRODUCT_NBR)
PARTITION BY CASE_N (
TXN_TOTAL< 1000,
TXN_TOTAL< 5000,
TXN_TOTAL< 10000,
NO CASE, UNKNOWN
) ;

沒有主索引 (No Primary Index)

小部份的資料表主要會針對每欄資料進行沒有主索引的資料表設計,每列資料會被隨機分散在 AMP 中,因為此資料表沒有主索引,也就是 Teradata Columnar,此時若只需針對少數欄位進行查詢,則 AMP 只需要針對欄資料對應的快取中就能將結果回傳。

建立沒有主索引資料表

1
2
3
4
5
6
7
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) NO PRIMARY INDEX

可是有趣的是雖然以欄為主的資料表沒有主索引,但還是能夠建立以欄為主的索引。

建立分區索引資料表 (By RANGE)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PARTITION BY (COLUMN,CASE_N (
TXN_TOTAL< 1000,
TXN_TOTAL< 5000,
TXN_TOTAL< 10000,
NO CASE, UNKNOWN
)),
UNIQUE INDEX(PRODUCT_NBR);

建立分區索引資料表 (By CASE)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PARTITION BY (COLUMN,CASE_N (
TXN_TOTAL< 1000,
TXN_TOTAL< 5000,
TXN_TOTAL< 10000,
NO CASE, UNKNOWN
)),
UNIQUE INDEX(PRODUCT_NBR);

事實上若只單純處理非結構化的資料透過 Teradata 進行 SQL 查詢速度是很理想,但往往面臨的問題主要是空間不足和記憶體不足等問題,因此除了設計最佳化的資料表之外,撰寫最佳化的 SQL 查詢也是非常重要的一件事。

相關資源