Teradata PI

可以说PI的合理运用是Teradata性能及各种优点的保障，那么到底怎么选择PI呢？资料上说的无非两种考虑，一是保证数据分布均匀，二是选择经常作为访问条件。但是日常工作中，我们经常会发现，现实可能并不是那么美好，经常会出现上述两中考虑相冲突的情况，也就是说能保障均匀分布的不是我们访问会用到的字段，常作为访问条件的字段不能保证数据均匀的分布。那怎么办呢？我现在的做法是，对于大表，优先考虑分布，小表优先考虑访问。有更好的方法么？

下面是资料对于PI的介绍：

[@more@]

Module 4：PI

1.Keys vs Indexes
• 键: 标示表和系统之间关系(PK)或表与表之间关系(FK)的数据模型概念.
• 索引: 文件系统机制.
-- 主索引:物理数据分布机制(它可能与PK不同),它是文件访问的主要路径.
-- 次索引:预备的文件访问路径,作为主索引的补充,较少用.
PK(Relation Rules)
PI(Physical Implementation)
May not contain duplicate values.
May be unique or non-unique.
May not be changed.
May be changed, but is considered an “unreasonable” update.
May not be NULL
May be NULL (1 row - UPI, many rows - NUPI)

2.PKs and Duplicate Rows
规则: PK必须唯一并且非空.
• 关系数据库排除重复行的规则,困扰整个行业几十年.
• Teradata中默认的SET Table不允许有重复行.
• Teradata的Multiset Tables允许重复行存在.
-- 这种表里面的索引必须是非唯一索引(NUPI and NUSI)以便允许重复值存在.
-- 唯一索引(UPI or USI)会阻止重复索引值的存在,因此也不允许重复行存在,即使表是Multiset.
• 如果SET表中不存在唯一索引,文件系统将对数据依照Row Hash逐字节比较,以确认表中数据的唯一性.
-- 许多非唯一索引(NUPI)导致重复行,并在重复行检查上花费很多时间.
-- 在做常规insert或update操作时,为了避免SET表中的重复行检查,可在表PK字段上指派一个唯一次索引(USI)以强制非唯一主索引(NUPI)表记录的唯一性.

3.CREATE TABLE – Primary Key / Index Rules
如果建表时没有明确指明PK,Teardata DBS总会将PK或唯一性限制列作为唯一性索引.
• 没有指定PI(DBS按以下优先级选择PI)
-- a.如果有PK,则PK = UPI
-- b.如果某列具有唯一性,则 col = UPI
-- c.没有唯一性的列,则 col = NUPI

• 指定了PI
-- 如果也指定了PK,且PK不为PI,则 PK = USI
-- 指定了行唯一性 col = USI

4.PI选择标准(Primary Index Choice Criteria)
PI选择主要依据以下标准:
• ACCESS
-- 单AMP操作最大化.
-- 选择访问频率最高的列.
-- join和值访问都需要考虑.
• DISTRIBUTION
-- 使并行处理最优化.
-- 选择能够使数据分布均匀的列.
• VOLATILITY
-- 尽量减少维护成本(I/O).
-- 选择值变动较少(stable data)的列.

5.PI特性(Primary Index Characteristics)
UPI可以提供最好的性能,最好的分布;NUPI提供较好的性能,较好的分布.
PI (UPI and NUPI)
• PI可能与PK不同.
• 每个表有且仅有一个PI.
• PI可能包含NULL值.
• 单值查询用单AMP,典型情况下,单I/O.
UPI
• 最多影响基表的单行记录.
• 任何时候都不需要临时文件(spool file).
• 系统自动保证索引值的唯一性.
NUPI
• 可能影响基表中的多行数据.
• 需要的时候会创建临时文件(spool file).
• 重复值存贮在相同AMP的相同数据块中.
• 如果所有的数据行在单一的数据块中,访问时只需要单一I/O.
• 如果SET表中没有指定USI,需要做重复值检查.

6.唯一性的级别和空间利用(Degree of Uniqueness and Space Utilization)
• PI的唯一性级别会直接影响空间的利用.索引的唯一性越好,空间的利用就会越好.
• 如果某列的不同值数目小于AMP个数,它不是好的PI选择.
• 有些NUPI可能会导致数据分布倾斜厉害.
• 唯一性PI或接近唯一性的PI可以提供较好的数据分布.

7.Multi-Column Primary Indexes
优点(多列 = 强唯一性)
• 具有唯一性的数据行增加.
• Rows/value 减少.
• 可选择性增加.
缺点(多列 = 较弱可用性)
• 这类PI只有在SQL语句中包含了所有PI组成列的值时才有效.
• 只取部分值不能哈希化.

8.PI的考虑(Primary Index Considerations)
• 基于PI列最多用来做访问条件,我们希望它是唯一或者近似唯一的.
• 选择可以均匀分布无倾斜的列.
• PI的值不应该经常变动.
• 不同值均匀分布在所有的AMP上.
-- 对大表来说,PI不同值的数目至少应该是AMP数的10倍.
• 可能的话,重复值尽量Hash到相同的AMP,存储在相同数据块中.
-- 重复值太多将会使用多数据块并导致多I/O操作.
-- 重复值太多可能导致存储倾斜厉害,并造成数据库空间不足的假象.
• SET表中的NUPI值重复太多会导致高重复值检查代价.

9.NUPI Duplicate Row Check
• SET表不允许重复值存在,当向NUPI的SET表插入新数据行时,系统必须执行NUPI重复值检查.
• 无论何时,只要可能,逐值限制NUPI的行数都是很重要的(最好是保持各值的NUPI行可以放到一个数据块中)
• 两种避免NUPI重复行检查的方法:
-- 利用USI来强制唯一性;
-- 创建multiset表,在multiset表中是不需要做行重复性检查的.

10.Column Distribution Demographics for a PI Candidate
列分布统计可以从以下4个方面着手:
不同值数目:
• 越多越好(与表记录数做比较,表明重复值少).
• 必须拥有足够的值以将数据分布到所有的AMP.
单个值的最大行数
• 越少越好.
空值最大行数
• 越少越好.
• 大数目的空值意味着数据分布的倾斜度极大.
• 大的倾斜度会导致严重的空间问题.
各值的典型行数
• 越少越好.
• 改变时的反映周期(Monitor periodically).

11.数据统计的查看SQL(SQL to View Data Demographics)
Max Rows per Value for 3 most frequent values:
SELECT t_colvalue, t_count
FROM (SELECT Last_name, COUNT(*)
FROM Customer GROUP BY 1) t_table (t_colvalue, t_count)
QUALIFY RANK (t_count) <= 3;

12.TableSize View(DBC.View)
example:
SELECT Vproc
,CAST (TableName AS CHAR(20))
,CurrentPerm
,PeakPerm
FROM DBC.TableSize
WHERE DatabaseName = USER
ORDER BY TableName, Vproc ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16723161/viewspace-1033593/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16723161/viewspace-1033593/