Oracle交换分区对数据的加载提速案例

oracle交换分区对数据的加载提速案例环境:os:linuxdb:oracle10g其中一个库的数据加载非常慢,如何能提高数据的加载速度呢?下

Oracle交换分区对数据的加载提速案例

环境:

os:linux

db:oracle10g

其中一个库的数据加载非常慢,如何能提高数据的加载速度呢?下面是一个小例子。

首先统计加载数据表所涉及的sql,这是做下面的基础。

使用表tabname1的sql如下几个:

SELECT COUNT(*)
FROM (SELECT RPL.ITEMCODE, RPL.CATALOGID
FROM tabname RPL
WHERE RPL.L3COLUMN = :1
and RPL.SUPPLIERID = :2
and RPL.STATDATE = TO_DATE(sysdate-2, 'YYYY-MM-DD')
GROUP BY (RPL.CATALOGID, RPL.ITEMCODE)) TEMP

SELECT SUM(LISTNUM) AS lSUM, SUM(CLICKNUM) AS CSUM
FROM tabname RPL
WHERE RPL.L3COLUMN = :1
and RPL.SUPPLIERID = :2
and RPL.STATDATE = TO_DATE(:3, 'YYYY-MM-DD')

select TMPB.*
from (SELECT TMPA.*, ROWNUM rownum_
FROM (SELECT TEMP.LSUM,
TEMP.CSUM,
TEMP.ITEMCODE,
TEMP.CATALOGID,
RPO.ORDERNUM,
RPO.ORDER_PRO_NUM,
TEMP.PRODUCTID
FROM (SELECT SUM(LISTNUM) AS lSUM,
SUM(CLICKNUM) AS CSUM,
RPL.ITEMCODE,
RPL.CATALOGID,
RPL.PRODUCTID
FROM tabname1 RPL
WHERE RPL.L3COLUMN = :1
and RPL.SUPPLIERID = :2
and (RPL.CATALOGID like '015%' or
RPL.CATALOGID like '15%')
and RPL.STATDATE = TO_DATE(:3, 'YYYY-MM-DD')
GROUP BY (RPL.CATALOGID, RPL.ITEMCODE, RPL.PRODUCTID)) TEMP
LEFT JOIN tabname3 RPO
ON TEMP.ITEMCODE = RPO.ITEMCODE
and RPO.STATDATE = TO_DATE(:4, 'YYYY-MM-DD')
ORDER BY LSUM DESC, TEMP.ITEMCODE) TMPA
WHERE ROWNUM <= :5) TMPB
WHERE TMPB.rownum_ > :6

SELECT TCC.DESCRIPTION
FROM tabname1 RPL, tabname2 TCC
WHERE RPL.COUNTRY = TCC.COUNTRYID
AND RPL.L3COLUMN = :1
and RPL.SUPPLIERID = :2
and RPL.ITEMCODE = :3
and RPL.STATDATE = TO_DATE(:4, 'YYYY-MM-DD')
and ROWNUM <= :5
ORDER BY RPL.LISTNUM DESC

通过以上sql可以看到,都是对数据某一天的统计,这些sql也是主要影响db磁盘的io的,所以建议调整tabname1的分区格式,采用rang-list组合分区;只创建分区索引,不创建全局索引。以STATDATE列创建rang分区,以L3COLUMN创建list分区

目前load数据逻辑:
1. 每天先truancate表tmp_tabname1,
2. 然后gp集群把这一天的所有数据都load到tmp_tabname1,
3. 然后再把tmp_tabname1 直接insert到表tabname1;速度主要慢在insert的过程。(可以按L3COLUMN把数据分配load到tmp_tabname1)

调整后load数据逻辑:
1. 每天先truancate表tmp_tabname1,
2. 然后gp集群把这一天的每个list(L3COLUMN)数据分别load到tmp_tabname1,也就是说gp集群把原来一次load变为1000次,或者tmp_tabname1的分区和tabname1一样,,这样gp集群也是一次load
3. 利用oracle的表交换技术(eg:alter table t_temp exchange subpartition p9sublist1 with table t_temp1 update indexes)来提高数据load到tabname1的速度

我测试用表交换技术和insert的load数据对比

数据量:300m,1700万记录的测试数据

用表交换(有全局索引的),速度比insert快2倍左右
用表交换(没有有全局索引的),速度比insert快4-6倍左右

这样做的好处,因为db的io瓶颈很严重,io的utile%几乎很少低于95%的
1. 提高查询速度,sql根据STATDATE找到rang主分区,然后再根据L3COLUMN找到list分区,这样就减少了数据扫描的数据量
2. 加快了load的速度
3. 便于维护

缺点:
1.gp集群到tmp_tabname1的load速度有影响

可以根据自己业务需求来选择合适实现方式,选择适合自己的就是最好的!!!

------end------

linux