测试环境：本地oracle，客户系统mysql

迁移

1.数据表迁移至oracle

采用两种方式 测试
1 kettle自带批量迁移方式
2 navicat自带数据传输工具
两种都可以实现数据迁移
方案：整体使用工具迁移数据，手工处理有问题的表，涉及部分字段处理，及可能出现的长度问题，以及字段大写可能需要注意
注意事项：
1 抽取后需要检查 表字段类型和精度 减少返工风险
2 数据大小写及符号半角全角问题
3 所有sql及建表语句统一使用大写字母

2.脚本调整

咨询过多个有迁移经验的人员，都没有采用批量调整脚本的方案，大体上简单sql脚本是通用的，但是函数及一些书写方式需要手工调整
函数部分规则表（待补充） ：为减少返工风险，初期先进行所有函数使用规定。如果需要调整，整体一起调整

函数说明 mysql函数 oracle函数
从左截取 left(字段,2) substr(字段,1,2)
从右截取 right(字段,4) substr(字段,-4)
日期减去1天 date_add(字段, interval -1 day) 日期字段-1
日期转化年月格式 date_format(字段,’%Y%m’) to_char(字段,’YYYYMM’)
日期转化年月日格式 date_format(字段,’%Y%m%d’) to_char(字段,’YYYYMMDD’)
日期减去日期得月 timestampdiff(month,日期,日期) months_between(日期字段,日期字段)
日期见日期得天 timestampdiff(day,日期,日期) to_number(日期-日期)
字符串转为日期 不需要转化 to_date(‘2004-02-03’,’yyyy-mm-dd’)
null值处理 ifnull(字段,0) nvl(字段,0)

优化方式

1.对抽出数据库的操作调整为数据库内操作（在mysql系统的测试）

例子：for_dws_shengyu_ziyuan_hetong.ktr
将脚本调整为库内操作后流程运行时间由798 s 降低为 160 s

2.SQL调整（调整为oracle可执行脚本）

例子：for_dws_shengyu_ziyuan_hetong.ktr
根据上面脚本调整后，在oracle数据库执行需要 37.801s

3.脚本优化（根据脚本直接时间决定是否修改，每次需要理清逻辑及测试数据）

例子：for_dws_shengyu_ziyuan_hetong.ktr
1过滤版本，采用分析函数
优化后，在oracle数据库执行需要 32.245s

4.表查询优化（oracle自带的执行计划工具）

explain plan for sql语句
select * from table(dbms_xplan.display)
根据sql执行计划，适当添加索引，及调整语句书写方式

5.流程优化

在以上步骤执行后，如果数据执行还是慢，将采用增量抽取方式， 保持当前全量的方式

调整前脚本

  1. select 
  2. distinct
  3.         c.z_area_e          as  quyu_bianma
  4.         ,c.z_area_text      as  quyu_mingcheng
  5.         ,c.z_city_e         as  chengshi_bianma
  6.         ,c.z_city_text      as  chengshi_mingcheng
  7.         ,c.z_proje_e        as  xiangmu_bianma
  8.         ,c.z_xmms_yx        as  xiangmu_mingcheng_yx
  9.         ,c.liangjia         as  zhuli_xiangmu
  10.         ,c.anjie_chengshi   as  dili_chengshi
  11.         ,a.z_sacfy          as  fangyuan_bianma  --  房源
  12.         ,a.z_sac_text       as  fangyuan_mingcheng  --  房源名称
  13.         ,a.z_prdtyp6        as  chanpin_lx_bianma  --  六级产品类型
  14.         ,a.z_ljms           as  chanpin_lx_mingcheng  --  六级产品类型描述
  15.         ,aa.sanji           as  sanji_chanpin  -- 三级产品
  16.         ,aa.qiji            as  qiji_chanpin   -- 七级产品
  17.         ,a.z_ldms           as  loudong_mingcheng  --  楼栋描述
  18.         ,a.z_syzyhtkjts     as  kucun_ts     --  合同口径库存套数
  19.         ,a.z_syzyhtkjzj     as  kucun_jine   --  合同口径库存金额
  20.         ,a.z_syzyhtkj_jz    as  kucun_mianji --  合同口径库存面积
  21.         ,a.z_htkjrq         as  kucun_banban
  22.         ,left(a.z_htkjrq,6) as  kucun_banban_yue
  23.         ,date_format(a.DB_DATE,'%Y%m%d')  as gengxin_riqi
  25.         ,(case  when   a.yqz_date !='00000000'  
  26.             then   a.yqz_date
  27.             end )  as  yushouzheng_riqi
  28.         ,(case when aa.sanji = '住宅'
  29.           and  a.yqz_date !='00000000'
  30.           and  right(left(a.z_htkjrq,6),2) in ('01','02','03','04','05','06')
  31.           and  left(a.yqz_date,4) < left(a.z_htkjrq,4) - 1     then  a.z_syzyhtkjzj
  32.           when aa.sanji = '住宅'
  33.           and  a.yqz_date !='00000000'
  34.           and  right(left(a.z_htkjrq,6),2) in ('07','08','09','10','11','12')
  35.           and  left(a.yqz_date,6) <= concat(left(a.z_htkjrq,4) - 1 ,'06')   then  a.z_syzyhtkjzj
  36.           when aa.sanji =  '商办'
  37.           and  a.yqz_date !='00000000'
  38.           and  right(left(a.z_htkjrq,6),2) in ('01','02','03','04','05','06')
  39.           and  left(a.yqz_date,4) < left(a.z_htkjrq,4) - 2    then  a.z_syzyhtkjzj
  40.           when aa.sanji = '商办'
  41.           and  a.yqz_date !='00000000'
  42.           and  right(left(a.z_htkjrq,6),2) in ('07','08','09','10','11','12')
  43.           and  left(a.yqz_date,6) <= concat(left(a.z_htkjrq,4) - 2 ,'06')   then  a.z_syzyhtkjzj
  44.           when aa.sanji =  '车位'
  45.           and  a.yqz_date !='00000000'
  46.           and  right(left(a.z_htkjrq,6),2) in ('01','02','03','04','05','06')
  47.           and  left(a.yqz_date,4) < left(a.z_htkjrq,4) - 3    then  a.z_syzyhtkjzj
  48.           when aa.sanji = '车位'
  49.           and  a.yqz_date !='00000000'
  50.           and  right(left(a.z_htkjrq,6),2) in ('07','08','09','10','11','12')
  51.           and  left(a.yqz_date,6) <= concat(left(a.z_htkjrq,4) - 3 ,'06')   then  a.z_syzyhtkjzj
  52.           end)  as  changkucun_jine    -- 长库存
  53.         ,(case when aa.sanji  != '住宅'  then  a.z_syzyhtkjzj
  54.           end)  as  feizhu_kucun_jine    -- 非住库存金额
  55.         ,(case when aa.sanji = '住宅'
  56.           and  a.yqz_date !='00000000'
  57.           and  a.z_syzyhtkjts  >0
  58.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 0     then  a.z_syzyhtkjzj
  59.           when aa.sanji =  '商办'
  60.           and  a.yqz_date !='00000000'
  61.           and  a.z_syzyhtkjts  >0
  62.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 1    then  a.z_syzyhtkjzj
  63.           when aa.sanji =  '车位'
  64.           and  a.yqz_date !='00000000'
  65.           and  a.z_syzyhtkjts  >0
  66.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 2    then  a.z_syzyhtkjzj
  67.           end)  as  nianchu_feichangku_quhua_riqi    --  年初非长库金额
  68.         ,(case when aa.sanji = '住宅'
  69.           and  a.yqz_date !='00000000'
  70.           and  a.z_syzyhtkjts  >0
  71.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 0     then  b.qianyue_yeji_riqi
  72.           when aa.sanji =  '商办'
  73.           and  a.yqz_date !='00000000'
  74.           and  a.z_syzyhtkjts  >0
  75.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 1    then  b.qianyue_yeji_riqi
  76.           when aa.sanji =  '车位'
  77.           and  a.yqz_date !='00000000'
  78.           and  a.z_syzyhtkjts  >0
  79.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 2    then  b.qianyue_yeji_riqi
  80.           end)  as  feichangku_quhua_riqi    --  年初非长库去化日期
  81.         ,(case when aa.sanji = '住宅'
  82.           and  a.yqz_date !='00000000'
  83.           and  a.z_syzyhtkjts  >0
  84.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 0     then  b.fangyuan_zongjia
  85.           when aa.sanji =  '商办'
  86.           and  a.yqz_date !='00000000'
  87.           and  a.z_syzyhtkjts  >0
  88.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 1    then  b.fangyuan_zongjia
  89.           when aa.sanji =  '车位'
  90.           and  a.yqz_date !='00000000'
  91.           and  a.z_syzyhtkjts  >0
  92.           and  left(a.yqz_date,4) >= left(a.z_htkjrq,4) - 2    then  b.fangyuan_zongjia
  93.           end)  as  feichangku_quhua_jine    -- 年初非长库去化金额
  94.         ,(case when a.yqz_date !='00000000'
  95.           and  a.z_syzyhtkjts  >0
  96.           and  timestampdiff(month,a.yqz_date,a.z_htkjrq)<5      then  a.z_syzyhtkjzj
  97.           end) as hetong_kucun_je_0_to_6_yue    -- 0-6月库存金额
  98.          ,(case when a.yqz_date !='00000000'
  99.           and  a.z_syzyhtkjts  >0
  100.           and  timestampdiff(month,a.yqz_date,a.z_htkjrq)>=5
  101.           and  timestampdiff(month,a.yqz_date,a.z_htkjrq)<11     then  a.z_syzyhtkjzj
  102.           end) as hetong_kucun_je_6_to_12_yue  -- 6-12月库存金额
  103.         ,(case when a.yqz_date !='00000000'
  104.           and  a.z_syzyhtkjts  >0
  105.           and  timestampdiff(month,a.yqz_date,a.z_htkjrq)>=11
  106.           and  timestampdiff(month,a.yqz_date,a.z_htkjrq)<23     then  a.z_syzyhtkjzj
  107.           end) as hetong_kucun_je_12_to_24_yue  -- 12-24月库存金额
  108.         ,(case when a.yqz_date !='00000000'
  109.           and  a.z_syzyhtkjts  >0
  110.           and  timestampdiff(month,a.yqz_date,a.z_htkjrq)>23     then  a.z_syzyhtkjzj
  111.           end) as hetong_kucun_je_24_yue       -- 24月以上库存金额
  112.         ,(case when a.yqz_date !='00000000'
  113.           and  aa.sanji  != '车位'
  114.           and  a.z_syzyhtkjzj  >0
  115.           and  a.z_syzyhtkjts  >0   then  timestampdiff(day,a.yqz_date,a.z_htkjrq)
  116.           end)  as feichewei_kucun_kl       -- 非车位库龄
  117.         ,(case when a.yqz_date !='00000000'
  118.           and  aa.sanji  != '车位'
  119.           and  a.z_syzyhtkjzj  >0
  120.           and  a.z_syzyhtkjts  >0   then  a.z_syzyhtkjts
  121.           end)  as feichewei_kucun_ts       -- 非车位套数
  122.         ,(case when aa.sanji  != '车位'  then  a.z_syzyhtkjzj
  123.           end)  as feichewei_kucun_je       -- 非车位存金额
  124. from 
  125. ( -- 每个月有个最新版本
  126.     select
  127.     date_format(date_add(DB_DATE, interval -1 day),'%Y%m')
  128.     ,max(date_format(DB_DATE,'%Y%m%d'))   as  yue_zuixin_gengxin_riqi
  129.     from sunac_syzytj_htkj
  130.     group  by 
  131.     date_format(date_add(DB_DATE, interval -1 day),'%Y%m')
  132. ) xin
  134. left join   sunac_syzytj_htkj a  on  date_format(DB_DATE,'%Y%m%d')=xin.yue_zuixin_gengxin_riqi
  136. left join  for_dws_yingxiao_taizhang  b  on  a.z_sacfy=b.fangyuan_bianma
  138. LEFT JOIN dim_cplx_w aa on a.z_prdtyp6 = aa.Z_PRDNUM6  -- 关联营销业态
  141. left join  
  142. (
  143.         select 
  144.             distinct
  145.             z_area_e 
  146.             ,z_area_text
  147.             ,z_city_e
  148.             ,z_city_text
  149.             ,z_proje_e
  150.             ,z_xmms_yx
  151.             ,liangjia
  152.             ,anjie_chengshi
  153.         from 
  154.         dim_xiangmu 
  155. ) c on a.z_proje_e = c.z_proje_e
  157. where    c. z_proje_e is not null     --  8530 条数据在dim 没关联上

调整后脚本

  2. with  data1  as(
  3. select  a.*,rank() over(partition by  to_char(DB_DATE-1,'YYYYMM') order by to_char(DB_DATE,'YYYYMMDD') desc) num  from "sunac_syzytj_htkj" a
  4. ) 
  5. select 
  6.         c.z_area_e          as  quyu_bianma
  7.         ,c.z_area_text      as  quyu_mingcheng
  8.         ,c.z_city_e         as  chengshi_bianma
  9.         ,c.z_city_text      as  chengshi_mingcheng
  10.         ,c.z_proje_e        as  xiangmu_bianma
  11.         ,c.z_xmms_yx        as  xiangmu_mingcheng_yx
  12.         ,c.liangjia         as  zhuli_xiangmu
  13.         ,c.anjie_chengshi   as  dili_chengshi
  14.         ,a.z_sacfy          as  fangyuan_bianma  --  房源
  15.         ,a.z_sac_text       as  fangyuan_mingcheng  --  房源名称
  16.         ,a.z_prdtyp6        as  chanpin_lx_bianma  --  六级产品类型
  17.         ,a.z_ljms           as  chanpin_lx_mingcheng  --  六级产品类型描述
  18.         ,aa.sanji           as  sanji_chanpin  -- 三级产品
  19.         ,aa.qiji            as  qiji_chanpin   -- 七级产品
  20.         ,a.z_ldms           as  loudong_mingcheng  --  楼栋描述
  21.         ,a.z_syzyhtkjts     as  kucun_ts     --  合同口径库存套数
  22.         ,a.z_syzyhtkjzj     as  kucun_jine   --  合同口径库存金额
  23.         ,a.z_syzyhtkj_jz    as  kucun_mianji --  合同口径库存面积
  24.         ,a.z_htkjrq         as  kucun_banban
  25.         ,substr(a.z_htkjrq,6) as  kucun_banban_yue
  26.         ,to_char(a.DB_DATE,'YYYYMMDD')  as gengxin_riqi
  27.         ,(case  when   a.yqz_date !='00000000'  
  28.             then   a.yqz_date
  29.             end )  as  yushouzheng_riqi
  31.         ,(case when aa.sanji = '住宅'
  32.           and  a.yqz_date !='00000000'
  33.           and  substr(substr(a.z_htkjrq,1,6),-2) in ('01','02','03','04','05','06')
  34.           and  substr(a.yqz_date,1,4) < substr(a.z_htkjrq,1,4) - 1     then  a.z_syzyhtkjzj
  36.           when aa.sanji = '住宅'
  37.           and  a.yqz_date !='00000000'
  38.           and  substr(substr(a.z_htkjrq,1,6),-2) in ('07','08','09','10','11','12')
  39.           and  substr(a.yqz_date,1,6) <= concat(substr(a.z_htkjrq,1,4) - 1 ,'06')   then  a.z_syzyhtkjzj
  41.           when aa.sanji =  '商办'
  42.           and  a.yqz_date !='00000000'
  43.           and  substr(substr(a.z_htkjrq,1,6),-2) in ('01','02','03','04','05','06')
  44.           and  substr(a.yqz_date,1,4) < substr(a.z_htkjrq,1,4) - 2    then  a.z_syzyhtkjzj
  46.           when aa.sanji = '商办'
  47.           and  a.yqz_date !='00000000'
  48.           and  substr(substr(a.z_htkjrq,1,6),-2) in ('07','08','09','10','11','12')
  49.           and  substr(a.yqz_date,1,6) <= concat(substr(a.z_htkjrq,1,4) - 2 ,'06')   then  a.z_syzyhtkjzj
  51.           when aa.sanji =  '车位'
  52.           and  a.yqz_date !='00000000'
  53.           and  substr(substr(a.z_htkjrq,1,6),-2) in ('01','02','03','04','05','06')
  54.           and  substr(a.yqz_date,1,4) < substr(a.z_htkjrq,1,4) - 3    then  a.z_syzyhtkjzj
  56.           when aa.sanji = '车位'
  57.           and  a.yqz_date !='00000000'
  58.           and  substr(substr(a.z_htkjrq,1,6),-2) in ('07','08','09','10','11','12')
  59.           and  substr(a.yqz_date,1,6) <= concat(substr(a.z_htkjrq,1,4) - 3 ,'06')   then  a.z_syzyhtkjzj
  61.           end)  as  changkucun_jine    -- 长库存
  63.         ,(case when aa.sanji  != '住宅'  then  a.z_syzyhtkjzj
  65.           end)  as  feizhu_kucun_jine    -- 非住库存金额
  67.         ,(case when aa.sanji = '住宅'
  68.           and  a.yqz_date !='00000000'
  69.           and  a.z_syzyhtkjts  >0
  70.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 0     then  a.z_syzyhtkjzj
  72.           when aa.sanji =  '商办'
  73.           and  a.yqz_date !='00000000'
  74.           and  a.z_syzyhtkjts  >0
  75.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 1    then  a.z_syzyhtkjzj
  77.           when aa.sanji =  '车位'
  78.           and  a.yqz_date !='00000000'
  79.           and  a.z_syzyhtkjts  >0
  80.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 2    then  a.z_syzyhtkjzj
  82.           end)  as  nianchu_feichangku_quhua_riqi    --  年初非长库金额
  84.         ,(case when aa.sanji = '住宅'
  85.           and  a.yqz_date !='00000000'
  86.           and  a.z_syzyhtkjts  >0
  87.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 0     then  b."qianyue_yeji_riqi"
  89.           when aa.sanji =  '商办'
  90.           and  a.yqz_date !='00000000'
  91.           and  a.z_syzyhtkjts  >0
  92.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 1    then  b."qianyue_yeji_riqi"
  94.           when aa.sanji =  '车位'
  95.           and  a.yqz_date !='00000000'
  96.           and  a.z_syzyhtkjts  >0
  97.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 2    then  b."qianyue_yeji_riqi"
  99.           end)  as  feichangku_quhua_riqi    --  年初非长库去化日期
  101.         ,(case when aa.sanji = '住宅'
  102.           and  a.yqz_date !='00000000'
  103.           and  a.z_syzyhtkjts  >0
  104.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 0     then  b."fangyuan_zongjia"
  106.           when aa.sanji =  '商办'
  107.           and  a.yqz_date !='00000000'
  108.           and  a.z_syzyhtkjts  >0
  109.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 1    then  b."fangyuan_zongjia"
  111.           when aa.sanji =  '车位'
  112.           and  a.yqz_date !='00000000'
  113.           and  a.z_syzyhtkjts  >0
  114.           and  substr(a.yqz_date,1,4) >= substr(a.z_htkjrq,1,4) - 2    then  b."fangyuan_zongjia"
  116.           end)  as  feichangku_quhua_jine    -- 年初非长库去化金额
  118.         ,(case when a.yqz_date !='00000000'
  119.           and  a.z_syzyhtkjts  >0
  120.           and  months_between(to_date(a.yqz_date,'yyyy-mm-dd'),to_date(a.z_htkjrq,'yyyy-mm-dd'))<5      then  a.z_syzyhtkjzj
  122.           end) as hetong_kucun_je_0_to_6_yue    -- 0-6月库存金额
  124.          ,(case when a.yqz_date !='00000000'
  125.           and  a.z_syzyhtkjts  >0
  126.           and  months_between(to_date(a.yqz_date,'yyyy-mm-dd'),to_date(a.z_htkjrq,'yyyy-mm-dd'))>=5
  127.           and  months_between(to_date(a.yqz_date,'yyyy-mm-dd'),to_date(a.z_htkjrq,'yyyy-mm-dd'))<11     then  a.z_syzyhtkjzj
  129.           end) as hetong_kucun_je_6_to_12_yue  -- 6-12月库存金额
  131.         ,(case when a.yqz_date !='00000000'
  132.           and  a.z_syzyhtkjts  >0
  133.           and  months_between(to_date(a.yqz_date,'yyyy-mm-dd'),to_date(a.z_htkjrq,'yyyy-mm-dd'))>=11
  134.           and  months_between(to_date(a.yqz_date,'yyyy-mm-dd'),to_date(a.z_htkjrq,'yyyy-mm-dd'))<23     then  a.z_syzyhtkjzj
  136.           end) as hetong_kucun_je_12_to_24_yue  -- 12-24月库存金额
  138.         ,(case when a.yqz_date !='00000000'
  139.           and  a.z_syzyhtkjts  >0
  140.           and  months_between(to_date(a.yqz_date,'yyyy-mm-dd'),to_date(a.z_htkjrq,'yyyy-mm-dd'))>23     then  a.z_syzyhtkjzj
  141.           end) as hetong_kucun_je_24_yue       -- 24月以上库存金额
  142.         ,(case when a.yqz_date !='00000000'
  143.           and  aa.sanji  != '车位'
  144.           and  a.z_syzyhtkjzj  >0
  145.           and  a.z_syzyhtkjts  >0   then   to_date(a.yqz_date,'yyyy-mm-dd')-to_date(a.z_htkjrq,'yyyy-mm-dd')
  146.           end)  as feichewei_kucun_kl       -- 非车位库龄
  147.         ,(case when a.yqz_date !='00000000'
  148.           and  aa.sanji  != '车位'
  149.           and  a.z_syzyhtkjzj  >0
  150.           and  a.z_syzyhtkjts  >0   then  a.z_syzyhtkjts
  151.           end)  as feichewei_kucun_ts       -- 非车位套数
  152.         ,(case when aa.sanji  != '车位'  then  a.z_syzyhtkjzj
  153.           end)  as feichewei_kucun_je       -- 非车位存金额
  155. from 
  156. data1 a
  157. left join  "for_dws_yingxiao_taizhang"  b  on  a.z_sacfy=b."fangyuan_bianma"
  158. left JOIN "dim_cplx_w" aa on a.z_prdtyp6 = aa.Z_PRDNUM6  -- 关联营销业态
  159. left join  
  160. (
  161.         select 
  162.             distinct
  163.             z_area_e 
  164.             ,z_area_text
  165.             ,z_city_e
  166.             ,z_city_text
  167.             ,z_proje_e
  168.             ,z_xmms_yx
  169.             ,liangjia
  170.             ,anjie_chengshi
  171.         from 
  172.         "dim_xiangmu"
  173. ) c on a.z_proje_e = c.z_proje_e
  175. where    
  176. a.num=1 and 
  177. c. z_proje_e is not null     --  8530 条数据在dim 没关联上

注意事项

1：