author：skate
time：2012/07/16

 

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