Teradata Join Index

TeradataのJoinは基本的にはHash Joinだけ。OracleでサポートされているNested Loop Join(NLJ)やBit Map Joinなどはない。しかし、DWHとしてExadataを使う場合にNLJなどが動いてしまったら返ってマイナスに働く。そういった意味でDWHシステムではHash Joinのみを使う。

Join Index（参考資料：Teradata Database : Database Desigin(B035-1094-115A)）
TeradataのJoinはHash Joinのみだが、Joinした状況でCompressされたTableを持つ機能がJoin Index機能です。実態を持っているという点でOracleのMaterialized Viewと同じだが、TeradataではView名などを意識しないで自然にアクセスができる。

以下のようなJoin Indexを作っておくと：

CREATE JOIN INDEX cust_ord2
AS SELECT cust.customerid,cust.loc,ord.ordid,item,qty,odate
FROM cust, ord, orditm
WHERE cust.customerid = ord.customerid
AND ord.ordid = orditm.ordid;

以下のようなパターンはJoin Indexを使って処理される：

SELECT cust.customerid, ord.ordid, item, qty
FROM cust, ord, orditm
WHERE cust.customerid = ord.customerid
AND ord.ordid = orditm.ordid
AND cust.loc = 'WI';

以下ではJoin Index外のTable(location)とJoin IndexがJoinされる；
SELECT cust.customerid,COUNT(ord.ordid)
FROM cust, ord, orditm, location
WHERE ord.ordid = orditm.ordid
AND cust.customerid = ord.customerid
AND cust.loc = location.loc
AND location.region = 'EUROPE'
AND EXTRACT(MONTH, ord.orddate) = 10
GROUP BY cust.customerid;

また、Joinをしない場合でも高速化するポイント(レンジ)を作り出すこともできる：

CREATE TABLE tp1 (
pid INTEGER,
name VARCHAR(32),
address VARCHAR(32),
zipcode INTEGER
);

CREATE JOIN INDEX tp1_ji AS
SELECT pid, name, zipcode
FROM tp1
WHERE zipcode >50000
AND zipcode < 55000;

以下のSQLはZIPコードのレンジが高速化ている：
SELECT pid, name
FROM tp1
WHERE zipcode IN (54455, 53066)
AND name = :N;

また、TPC-Hベンチマークを高速化するケースでは：

CREATE JOIN INDEX order_join_line
AS SELECT (l_orderkey, o_orderdate, o_custkey,
o_totalprice),(l_partkey, l_quantity,
l_extendedprice, l_shipdate)
FROM lineitem
LEFT JOIN orders ON l_orderkey = o_orderkey
ORDER BY o_orderdate
PRIMARY INDEX (l_orderkey);

以下のAverage集計が高速化される：
SELECT l_partkey, AVG(l_quantity),
AVG(l_extendedprice)
FROM lineitem , orders
WHERE l_orderkey = o_orderkey
AND o_orderdate > '1997-11-01'
GROUP BY l_partkey;

あるいは、以下のように集計しておいてJoin Indexを作成することもできる(Aggregate Join Index):

CREATE JOIN INDEX ord_cust_idx AS
SELECT c_nationkey, SUM(o_totalprice(FLOAT)) AS price, o_orderdate
FROM orders, customer
WHERE o_custkey = c_custkey
GROUP BY c_nationkey, o_orderdate
ORDER BY o_orderdate;

SELECT COUNT(*), SUM(o_totalprice)
FROM orders, customer
WHERE o_custkey = c_custkey
AND o_orderdate > DATE ‘1998-09-20’
AND o_orderdate < DATE ‘1998-10-15’
GROUP BY c_nationkey;