数据表分区解决方案(转)
2010-07-09 11:22 假面Wilson 阅读(480) 评论(0) 编辑 收藏 举报
数据表分区解决方案
问题归纳：
数据仓库根据不同的业务情况数 据的量也是不同的。对于数据仓库来说，处理的时候是需要对相应的表进行查询的，因此对查询的优化也就是对整个处理过程的优化。我们一般对那些数据量比较大 的度量值表进行按年建表，或者按月建表的方式来优化其查询的速度。但是有时候某些维度表的数据也会非常的多（数据仓库中维度表不能使用分区），或者是已经 按月建表的但是每个月的数据量仍然非常大，严重的影响了数据仓库的处理性能。
方案提出：
对那些数据量非常大的表进行分区处理，根据不同的情况按照日期或者ID进行拆分
处理方式：
在新建数据库的时候，建多个文件组，用来存储不同分区函数来分隔的数据，每个文件组分别建多个数据文件，减少I/O的读写。建议每个数据文件不要大于3G。以后可以动态添加数据文件
1、那些按年建的表，按照季度分成4个分区，1一个季度对应一个文件组来存放
按时间方式来分区的函数和方案代码如下：
CREATE PARTITION FUNCTION [PF_DATETIME_2008](numeric(18,0)) AS RANGE RIGHT FOR VALUES (200804010000, 200807010000, 200810010000)
注：日期的分割节点分别为4月1日，7月1日和10月1日
CREATE PARTITION SCHEME [PS_DATETIME_2008] AS PARTITION [PC_DATETIME_2008] TO ([PRIMARY], [FG2], [FG3], [FG4])
注:根据函数中的3个分割点，分别存储在4个不同的文件组里
建数据表的时候语句如下:
create table tb_pf
(
id int,
datetime numeric(18,0)
)
on PS_DATETIME_2008(datetime)
2、对于那些按月建表的或者是没有日期标识的数据表，我们可以利用其自增长的ID来分割分区
按ID方式来分区的函数和方案代码如下：
CREATE PARTITION FUNCTION [PF_ID](bigint) AS RANGE RIGHT FOR VALUES (20000000, 40000000, 60000000)
注：ID的分割节点分别为20000000，40000000和60000000
CREATE PARTITION SCHEME [PS_ID] AS PARTITION [PF_ID] TO ([PRIMARY], [FG2], [FG3], [FG4])
建数据表的时候语句如下:
create table tb_pf
(
id int,
datetime numeric(18,0)
)
on PS_ID(id)
实施效果：
对一个有2500多万数据的分局数据表insert和update操作比一个没有进行分区的数据表性能提高约10%左右
问题归纳：
数据仓库根据不同的业务情况数 据的量也是不同的。对于数据仓库来说，处理的时候是需要对相应的表进行查询的，因此对查询的优化也就是对整个处理过程的优化。我们一般对那些数据量比较大 的度量值表进行按年建表，或者按月建表的方式来优化其查询的速度。但是有时候某些维度表的数据也会非常的多（数据仓库中维度表不能使用分区），或者是已经 按月建表的但是每个月的数据量仍然非常大，严重的影响了数据仓库的处理性能。
方案提出：
对那些数据量非常大的表进行分区处理，根据不同的情况按照日期或者ID进行拆分
处理方式：
在新建数据库的时候，建多个文件组，用来存储不同分区函数来分隔的数据，每个文件组分别建多个数据文件，减少I/O的读写。建议每个数据文件不要大于3G。以后可以动态添加数据文件
1、那些按年建的表，按照季度分成4个分区，1一个季度对应一个文件组来存放
按时间方式来分区的函数和方案代码如下：
CREATE PARTITION FUNCTION [PF_DATETIME_2008](numeric(18,0)) AS RANGE RIGHT FOR VALUES (200804010000, 200807010000, 200810010000)
注：日期的分割节点分别为4月1日，7月1日和10月1日
CREATE PARTITION SCHEME [PS_DATETIME_2008] AS PARTITION [PC_DATETIME_2008] TO ([PRIMARY], [FG2], [FG3], [FG4])
注:根据函数中的3个分割点，分别存储在4个不同的文件组里
建数据表的时候语句如下:
create table tb_pf
(
id int,
datetime numeric(18,0)
)
on PS_DATETIME_2008(datetime)
2、对于那些按月建表的或者是没有日期标识的数据表，我们可以利用其自增长的ID来分割分区
按ID方式来分区的函数和方案代码如下：
CREATE PARTITION FUNCTION [PF_ID](bigint) AS RANGE RIGHT FOR VALUES (20000000, 40000000, 60000000)
注：ID的分割节点分别为20000000，40000000和60000000
CREATE PARTITION SCHEME [PS_ID] AS PARTITION [PF_ID] TO ([PRIMARY], [FG2], [FG3], [FG4])
建数据表的时候语句如下:
create table tb_pf
(
id int,
datetime numeric(18,0)
)
on PS_ID(id)
实施效果：
对一个有2500多万数据的分局数据表insert和update操作比一个没有进行分区的数据表性能提高约10%左右
