数仓体会之（分区与索引）

--------------------有些坑，我早已跳过，但，槽跳了，坑也要继续跳 ，在此记录些工作的杂七八的事
此内容限于oracle数据库进行讨论。

针对目前个人在实施数据仓库中，发现索引及分区基本上没有套路，基本是想怎么玩就怎么玩，而且基本是由于后期查询速度慢，然后是随意的添加索引，一个表十多二十个索引都随处可见。在此个人对索引与分区提出些个人意见，仅供参考，欢迎讨论。

设计原则：

不管是分区还是索引，它的作用都是为了快速的定位数据，减少IO扫描，因此，一个表在字段上建立分区与索引，都必须针对这个表上的查询有对应的过滤条件。由于索引会增加过多存储，且会降低插入速度，因此，当一个表过大时，首先应该考虑是否可以分区，如何分区，在这基础之后再考虑索引。

分区：

原则上，三年数据量比较大(比如超过一个定义值300万)，就应该进行表分区设计。分区键确定也应该由专人共同商得结论。

关于分区，无特殊情况，个人建议采用间隔分区，可以针对时间数字进行分区,以下以时间和月份为例说明：

1，所属期止分区(按月)，也可按天和按年，

PARTITION BY RANGE (SSQZ)

INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))

(

PARTITION P1 VALUES LESS THAN (TO_DATE('2010-1-1', 'YYYY-MM-DD'))

)

2,kpyf_dm 类型为数字 分区采用间隔

PARTITION BY RANGE (KPYF_DM)

INTERVAL (1)

(

PARTITION P1 VALUES LESS THAN (201001)

)

使用这种分区的好处在于，维护简单，不用和list分区一样事先定义好内容，便于扩展便于维护。

比如系统中数据量偏大各税种申报表、申报附表、资产负债损益表，这些数据都可以采用以上的第一种分区，而针对电子抵帐中的增值税普通及专票，都可以采用第二种情况分区。

以上分区方式因为是自动扩展方式，因此删除分区时应该特殊考虑这个分区是否存在，

建议做法，删除分区，由于这个是系统动态分区，所有分区名不固定，

先查询这个分区中是否有记录，有再进行删除，无的话不用进行删除。

以下用时间分区为例，这个时间只要是属于这个分区的任何一天都可以.

先判读是否这个分区有数据，

select count(1) into ln_num from xxx where 对应分区查询内容 and rownum=1。

若 ln_num>0则执行以下操作

ALTER TABLE xxxx TRUNCATE PARTITION for (date '2015-01-01')。

目前各省数据来源都比较类似，针对模型进行分区设计，个人感觉这个应该是与模型一同设计好，不会和区域有太大关系。

针对目前系统中多数用征期id(zq_id）来表示所属期，个人感觉没有太大意义，坏处多于好处，不管是查询还是转换，还是存储都增加了不必要的麻烦。

索引:

个人比较喜欢分区+全表扫描方式，感觉oracle的能力已经很强，或者再加上并行，几百万的数据应该都是秒级别出来。但是，慢总是存在的。“加工慢，查询慢，加个索引吧"这可能是现在的通用套路。以下是江西这边一个纳税人维度表的索引

一个一百八十万记录的表有36个索引，在此不说合理性，只讨论现象

数据准备区：

由于数据准备区目前只是启到一个过渡作用，一般不会直接对此表进行查询，

因此，一般建立两个索引，

1，源表中主键对应索引，建议单列，目前金三系统一般为单列主键， 若其它多列则可合并为单列，也可根据其中多列中挑选值多的列建立普通索引。

此字段的作用主要是用于增量的一些删除操作， 目前的做法基本就是建立源表的主键，这个主要是以删除策略有关，比如一些申报附表，若它的删除是根据sb_sbb表来进行删除的，那么，申报附表中可建立的是sbuuid的索引。

2，用于后续s0层增量加工的索引，目前很多是 数据集成时间(sjjcsj),个人建议后续加工还是和准备区加工一样，使用数据同步时间(sjtb_sj) , 为什么在此不讨论。

除以上两索引外，对于一些主从表合并加工时是否需要索引，一般情况，hash join 在数据量很大情况下是有优势，数据量少情况也不会太慢， 而增加主表外键索引则嵌套循环联接，数据量不少情况下可能会更慢。

另：源表中可能建立了分区的，此处建设保留，分区方式可和源系统一致， 主要作用是此类数据过大，初始化时可能要根据分区来进行操作。

数据仓库：

尽量先考虑分区 ， s0,s1,s2 主键不建议用多列复合主键，若有些增量加工直接按月批量加工则可不建立主键索引（有特定查询除外，如发票代码、发票号码之类)。

s3,s5层的聚合表，不需要建立主键，理论上这类表都是随着时间进行数据累计，因此，分区后应可以解决数据量过大问题，

理论上大表若分区合理，加上本地索引，后期的查询效率是可以控制，但若只建立索引，就算目前的效率可行，随着时间的积累，查询效率必然会越来越慢。

后话：当然，数据仓库的Etl、数据展现的 效率 ，不只是表分区与索引就能解决，这只是其中一小部分，模型设计、sql优化，以及抽取框架等都是比较重要的因素。

而对于数据的 准确性 ，则更多的是对源数据的认识，目标模型的理解，出错处理的健壮性等，而更重要的是，如何快速的确定数据的正确与否，或者说如何快速的找到或定位程序的问题之处。这个更是我们应该思考和解决的问题。

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

转载于:http://blog.itpub.net/134308/viewspace-2140292/