Teradata join
1.SELECT Statement ANSI Join Syntax
版本V2R2以后,Teradata支持ANSI join语法及外连接:
SELECT colname [, colname , …]
FROM tabname [aname]
[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
tabname1 [aname]
ON condition ;
INNER JOIN: 所有匹配的行.
LEFT OUTER JOIN: 以左边表为准,右边不能匹配的行填NULL.
RIGHT OUTER JOIN: 以右边表为准,左边不能匹配的行填NULL.
FULL OUTER JOIN : Both tables are used to qualify and extended with nulls.
CROSS JOIN : Product join or Cartesian product join.
2.Example of ANSI and Teradata JOIN Syntax
ANSI语法:
SELECT D.Department_Number AS "Dept Number"
,D.Department_Name AS "Dept Name"
,E.Last_Name AS "Last Name"
,E.Department_Number AS "Emp Dept"
FROM Department D
INNER JOIN Employee E
ON E.Department_Number = D.Department_Number;
Teradata语法:
SELECT D.Department_Number AS "Dept Number"
,D.Department_Name AS "Dept Name"
,E.Last_Name AS "Last Name"
,E.Department_Number AS "Emp Dept"
FROM Department D
,Employee E
WHERE E.Department_Number = D.Department_Number;
注:上述sql虽没有明确定义为inner join,但Teradata解析器仍会将其解释为inner join.
3.Join Processing
表与表之间的left outer join主要有以下形式：
– product join,
– merge join,
– hash join
– nest join
– exclusion join
而较常见的是product join和merge join。
做join的行必须在相同的AMP上:
如果需要,系统将创建一行或多行的spool拷贝并将其移动到公共AMP
连接处理从不移动或改变任何源表数据.
优化器基于以下原则选择最好的join方式:
有用的索引
统计信息 (COLLECTed STATISTICS or Dynamic Sample)
四种join的区别可类似于两数组的比较来解释:
假定有A,B两个数组,现在需要得到两数组中的相同元素,有以下几种方法:
算法1: A,B均先排序,再做比较
算法复杂度：o(nlogn)-------->Merge join
算法2: A,B均不排序,做比较
算法复杂度：o(n^2)-------->Product join
算法3: A排序,B不排序,B通过二分法做比较
算法复杂度介于前两者之间------>Hash join
注:选择merge join 还是product join主要是看比较时间长还是排序时间长.
4.Optimizer Minimizes Spool Usage
优化器利用以下方法将spool空间最小化:
仅投影(拷贝)查询需要的列.
先做单表关联(减少数据行).
只要可能,只将小表置于spool空间.
优化器在做join前将spool空间最小化:
Applies SET conditions first (WHERE).
Only the necessary columns are used in Spool.
5.Row Selection
只要可能,在join时加上行选择条件
列投影永远先于join,行选择一般先于join.
技巧:减少参与join的行数可大大提升join效率.
6.Join Redistribution
两表关联，若被关联的两个字段均不为二表的PI，则需要做下列操作之一：
1>重新分布：将被关联的字段设为PI,数据做重新分布；
2>小表复制：将小表的内容复制到一个AMP上，最终所有AMP均包含小表所有数据
两表做Merge join时,有三种情况:
最好情况:参与join的列分别是两表的PI,join操作可立刻执行.
-- 数据已经在相同的AMP上,不需要数据转移到其他AMP上;
-- 数据行已经按照哈希排序存储,不需要做排序操作.
次之:参与join的列有一个是表的PI,另一个不是.
-- 非PI表数据必须按照参与join的列值做hash重分布到目标AMP上;
-- 如果是小表,将全表复制到所有AMP上以便做join;
-- 如果表较大,将涉及到的行拷贝到目标AMP上.
糟糕情况:参与join的列均不是表的PI.
-- 两个表将均依照join字段做重分布(或小表复制)
7.Duplicating a Table in Spool
对merge join,优化器可能选择复制小表到每个AMP.
对product join,优化器总会复制一个表到所有AMP.
对以上两种情况,每个AMP都必须要有足够的空间以保证数据复制完成.
8.Merge Join
被关联的数据行必须在相同AMP上:
Merge Join reads blocks from both tables only once.
Usually chosen for an equality join condition.
Generally more efficient than a product join.
Merge join步骤:
识别小表.
如果需要的话:
– Put qualifying data of one or both tables into spool(s).
– Move the spool rows to AMPs based on the join column hash.
– Sort the spool rows into join column hash sequence.
利用hash值做数据匹配.
9.嵌套关联(Nested Joins)
一种特殊的关联.
唯一的一种不会总是涉及到所有AMP的关联.
从系统资源方面来说，这是最高效的一种方式.
OLTP应用的最好选择.
优化器必须拥有以下信息才会选择Nested Join:
-- 第一个表中(UPI或USI)的等值.
-- 第二个表中相对于索引具有唯一值的列.
系统从表1中得到唯一数据行.
利用hash值得到表2中匹配的数据行.
example:
SELECT E.Name,D.Name
FROM Employee E
INNER JOIN Department D
ON E.Dept = D.Dept
WHERE E.Enum = 5;
10.乘积join(Product Join)
下列情况均可产生product join:
The WHERE clause is missing.
A Join condition is not based on equality (<>, LESS THAN, GREATER THAN).
Join conditions are OR together.
There are too few Join conditions.
A referenced table is not named in any Join condition.
Table aliases are incorrectly used.
The Optimizer determines that it is less expensive than the other Join types.
Does not sort the rows.
May re-read blocks from one table if AMP memory size is exceeded.
It compares every qualifying Table1 row to every qualifying Table2 row.
Those that match the WHERE condition are saved in spool.
It is called a Product Join because:Total Compares = # Qualified Rows Table 1 * # Qualified Rows Table 2
The internal compares become very costly when there are more rows than AMP memory can hold at one time.
They are generally unintentional and often give meaningless output.
Product Join process:
-- Identify the Smaller Table and duplicate it in spool on all AMPs.
-- Join each spool row for Smaller Table to every row for Larger Table.
11.Cartesian Product
This is an unconstrained Product join.
Each row of Table1 is joined to every row in Table2.
Cartesian Product Joins consume significant system resources.
Cartesian Product Joins rarely have practical business use.
Cartesian Product Joins frequently occur when:
-- A join condition is missing or there are too few join conditions.
-- Join conditions are not based on equality.
-- A referenced table is not named in any join condition.
-- Table aliases are incorrectly used.
The transaction aborts if it exceeds the user’s spool limit.
12.Hash Join
Hash Join:小表依照row hash顺序存储,然后重分布或复制到所有的AMP.然后大表开始每行一条的处理数据,所以不需要将大表按照hash顺序存储.
可用以下DBS空值列来打开hash join和为hash join分配空间:
HTMemAlloc
SkewAllowance
优化技术可有效的将小表放入cache,然后将其与spool中未排序的大表做join.
Row Hash Join Process:
Identify the smaller table.
Redistribute or duplicate the smaller table in memory across the AMPs.
Sort the cache memory into join column row hash sequence.
Hold the rows in memory.
Use the join column row hash of the larger table to binary search memory for a match.
这类join避免大表排序,但有时会大表复制或重分布.
13.Exclusion Joins
返回无匹配记录的行.
可能是merge或product join.
导致NOT IN字句和EXCEPT操作.
在可空列上使用3种逻辑值.
如果可能在建表时对可能出现NOT IN操作的列加上NOT NULL属性.
说明: 在可空字段上做join时加上 WHERE colname IS NOT NULL 条件.
Exclusion Joins(NOT IN)的三条规则:
Any True – 滤掉
Any Unknown – 滤掉
All False – 保留.
14.(多表关联)n-Table Joins
优化器一次只能处理处理两个表.
join的结果再与第三个表做join.
所有的多表关联都会被分解为两表连接.
优化器自动尝试决定最好的join顺序.
收集关联字段统计信息可以帮助优化器做好的选择.
SELECT …. FROM Table_A, Table_B, Table_C, Table_D WHERE . . . ;
15.Join Considerations with PPI
PPI是基于Teardata已有应用的木块扩展,因此,所有的join算法均支持PPI
同NPPI相比,用PPI时,如果在查询约束中,大数目的分区没有消除,Row Hash Merge Join的性能可能会比较糟.
原因:
同NPPI相比,PPI的Row Hash Merge Join算法比较复杂并且需要更多的资源(假设有效数据块数目相等).
since rows are not in hash order, but rather in partition/hash order.
对两个PPI不同的表做关联,对一个PPI表和一个NPPI表做关联,有以下三种方法:
One option is to spool the PPI table (or both PPI tables) into a non-PPI spool file in
preparation for a traditional merge join.
A second option (not always available) is to spool the non-PPI table (or one of the two PPI
tables) into a PPI spool file, with identical partitioning to the remaining table, in preparation
for a rowkey-based merge join.
The third approach is to use the sliding window join of the tables without spooling either one.
The optimizer will consider all reasonable join strategies, and pick the one that has the best-estimated performance.
NPPI与PPI做等值连接:
1)、分区较少的情况:
Teradata将保持NPPI表中的数据块,PPI表中的数据每个分区一块,以提高join性能.
即使没有分区被消除,性能与NPPI表到NPPI表的连接相当(假定分区极少)
– 相同数目的磁盘I/O (排除非常规情况 - 一个hash值对应多个数据行)
– 内存需求高
– CPU利用稍高
– 如果加上分区限制,查询效率将要高得多.
2)、分区较多的情况
Teradata保持NPPI表的块,将PPI表的块尽可能多的装入内存,利用滚动窗口技术让连接效率最佳
这类连接的效率一般NPPI到NPPI表的join要低,除非分区限制可以很大缩减工作量
-- 更多数目的磁盘I/O (NPPI表的数据块必须重复扫描多次).
NPPI与PPI表join的I/O数目是:(p/k * d1) + d2
NPPI与NPPI表join的I/O数目是:d1 + d2
(说明: d1 = NPPI表的数据块数目
d2 = PPI表的数据块数目
p = 参与join的分区数目
k = PPI表可以加到内存中的分区数 )
-- 需要更多的内存
-- 需要更多的CPU资源
为了得到较好的性能,在查询中尽量多的限制以消除最多的分区
-- p/k的值要尽量的小,即往内存中加入数据的次数尽量少
3)、滑动窗口
最直接的连接NPPI与PPI表的方法是将NPPI表与PPI表的逐个分区做关联,也就是将整个关联变化成一些列的子关联.
这样操作的效率可能较低,特别是NPPI表特别大时
16.Join Processing Summary
product join和merge join的概念
1)product join就是乘积关联，做法是对左表的每一条记录都跟右表的所有记录进行关联（即所说的笛卡尔积），然后根据关联字段，筛出符合关联条件的记录。此法的缺点是作此关联所付出的系统的CPU和IO代价较大（由于笛卡尔积的关系） 但相对与merge join来说它的优点是左表和右表都不需要排序，因此teradata优化器只在右表记录数和导出字段字节数乘积较小的情况下（一般凭经验，记录数小于25条以下，字段字节数没测过）或者关联条件包含非等式关联或者关联条件中带有OR时才选择product join；
2)merge join就是合并关联，做法是对左右表分别进行排序，然后根据关联条件进行匹配关联。其优点时每一个左表记录只会跟右表的一条或某几条记录进行关联，大大减少了CPU和IO的花费，但缺点是对于右表记录数较少的情况下，对非常大的左表进行排序较浪费数据库资源。总的说来，merge join是一种有较高效率的关联算法，因此teradata优化器一般情况下都采用merge join,只在右表记录数和导出字段字节数乘积较小的情况下采用product join
重分布还是全拷贝
由于teradata数据库是一种分布式并行数据库，记录是根据特定字段的值通过哈西计算后分布在多个AMP上，因此要实现关联运算，除了采用何种关联算法（即采用product join还是merge join，还是。。。）的问题，还有左表和右表如何分布到多个AMP上的问题。一般情况下表是根据PI值做分布，但是关联时不是。
1)对于右表记录数较少的情况下，teradata优化器会将右表的记录在每一个AMP上都复制一份，也就是说如果数据库有50AMP,10条记录的表经过拷贝后在数据库中就有500记录，此种做法就是全拷贝。可以想象此种做法同product join一样，在右表记录数较多情况下，比较浪费系统的IO资源和SPOOL空间,但是它也有好处，好处是它能够避免左表的重分布（下面提到的概念）。
2）如果右表是分区导出表，teradata优化器除了主索引关联的情况下，无法如何总是采用全拷贝的方法分布右表.
3)如果右表记录数较多，同时不是分区导出表的情况下，teradata优化器会将左右表根据关联字段重新计算HASH值,然后根据HASH值做AMP的分布，即关联字段替代了PI成为SPOOL空间中关联表的主索引.此法就叫做重分布.其优点是减低了系统的IO资源和SPOOL的空间的使用(表记录数在重分布前后不变）,但缺点是由于重分布，导致了节点间的数据移动和bynet的负荷增大,同时导致了AMP数据分布的不平均。
多关联SQL
对于一个左表（主表）同多个右表（维表，关联表）关联时，teradata总是一个一个的关联，即先拿左表同右表1关联生成新的左表，然后再拿新的左表同右表2关联生成新的左表，如此反复，直到右表都关联完毕。此法的效率相当有问题。当左表较大，而右表较小的情况，每做一次关联，就要进行哈希重分布或全拷贝，同时还进行一次表的全扫描，资源浪费较大。
关于优化的一些建议
1)对于左表极大而右表的记录数较少的情况下，建议通过使用导出分区表强制teradata优化器使用全拷贝，避免了左表的重分布和数据分布不均匀
2)对于右表极大但是又是分区导出表的情况下,建议采用无分区的临时表，先将分区导出表插入到临时表，通过左表和临时表进行关联，以避免teradata优化器使用全拷贝的方式
3)多于多关联SQL,可以建立临时表,先将多个很小的右表通过笛卡尔积形成大表（注意控制大表条数，一般控制在千万级效果较佳）,然后再将左表同关联好的右表进行关联。
teradata对于表级的left outer join的SQL优化
一般说来:表与表之间的left outer join主要有四种形式：product join,merge join,hash join和nest join 而较常见的是product join和merge join。