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------