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