作者：mobiledu2502857673 | 来源：互联网 | 2014-07-11 17:33
oracle交换分区对数据的加载提速案例环境：os：linuxdb：oracle10gwww.2cto.com其中一个库的数据加载非常慢，如何能提高数据的加载速度呢？下面是一个小例子。首先统计加载数据表所涉及的sql，这是做下面的基...SyntaxHighlighter.all
oracle交换分区对数据的加载提速案例
环境：
os：linux
db：oracle10g
www.2cto.com
其中一个库的数据加载非常慢，如何能提高数据的加载速度呢？下面是一个小例子。
首先统计加载数据表所涉及的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, &＃39;YYYY-MM-DD&＃39;)
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, &＃39;YYYY-MM-DD&＃39;)
www.2cto.com
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 &＃39;015%&＃39; or
RPL.CATALOGID like &＃39;15%&＃39;)
and RPL.STATDATE = TO_DATE(:3, &＃39;YYYY-MM-DD&＃39;)
GROUP BY (RPL.CATALOGID, RPL.ITEMCODE, RPL.PRODUCTID)) TEMP
LEFT JOIN tabname3 RPO
ON TEMP.ITEMCODE = RPO.ITEMCODE
and RPO.STATDATE = TO_DATE(:4, &＃39;YYYY-MM-DD&＃39;)
ORDER BY LSUM DESC, TEMP.ITEMCODE) TMPA
WHERE ROWNUM <= :5) TMPB
WHERE TMPB.rownum_ > :6
www.2cto.com
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, &＃39;YYYY-MM-DD&＃39;)
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倍左右
www.2cto.com
这样做的好处，因为db的io瓶颈很严重，io的utile%几乎很少低于95%的
1. 提高查询速度，sql根据STATDATE找到rang主分区，然后再根据L3COLUMN找到list分区，这样就减少了数据扫描的数据量
2. 加快了load的速度
3. 便于维护
缺点：
1.gp集群到tmp_tabname1的load速度有影响
可以根据自己业务需求来选择合适实现方式，选择适合自己的就是最好的！！！
------end------
作者 skate
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 