Bulk Collect

总结：

1.forall 比 for loop要高一倍的效率
2.当使用limit时，open cursor 的退出使用exit when t_data.Count=0，不要使用exit when v_cur%notfound，否则取不到limit的余数
3.使用limit控制Oracle内存使用，否则超出内存大小将写到磁盘，反而拖慢数据提取效率,目前使用limit=100w，
  Oracle服务器内存48G，粗略统计，开启3个并行调用，占用60%内存
4.Type t_array is table Of TAB_DW_HS_BDZLXX_LS%rowtype;和
  forall i in 1 .. t_data.Count insert Into TAB_DW_HS_BDZLXX_LS values t_data(i);无法使用动态sql实现(execute immediate)
故改造此过程为sql代码块，然后放到shell中使用shell参数实现动态效果
5.此代码块允许重入，编写外壳为此代码块提供并行能力，并行数取决于Oracle服务器的内存和计算资源
  ps：在老仓库上开启20并行导致其他Oracle会话无法登陆且在执行sql计算缓慢
6.单个会话处理效率为100W记录/分，环境：48G内存，HP小机

以下是用于调试的存储过程，使用时再修改为代码块，在shell中传入p_mv_name和p_tb_name两个参数

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

create or replace procedure SP_ODS2DW_TEST(

p_mv_name In Varchar2,
p_tb_name In Varchar2  
) 
Is

/*
说明：
ods2dw的全量加载脚本，支持跨库，使用同义词作为数据源(odm)


zhonglei 20130527


依赖：
etl_user.etl_dw_ctrl
etl_user.etl_dw_fb
etl_user.log_dw_table


入参：
$1 mv_name ODM的MV名称
$2 tb_name DW表名称


所需硬代码替换：
1.Type t_array is table Of $2%rowtype;
2.forall i in 1 .. t_data.Count insert Into $2 values t_data(i);



  
*/
--两个入参
--p_mv_name Varchar2(64);
--p_tb_name Varchar2(64);


--打开数据源的游标
TYPE type_cur IS REF CURSOR;
v_cur type_cur;


--Table集合类型,shell入参 $2 替换 TAB_DW_HS_BDZLXX_LS
Type t_array is table Of TAB_DW_HS_BDZLXX_LS%rowtype;
t_data t_array;


--
v_total Number;
v_limit Number; 
v_sql Varchar2(2000);
v_exec_date varchar2(8);
v_errmsg Varchar2(2000);
v_column Varchar2(2000);


--分区参数
v_partition varchar2(200);
v_where varchar2(200);
v_partitionType char(1);
v_partitionAreaFiled varchar2(100);
v_partitionYearFiled varchar2(100);
v_partitionSourceType char(1);


--概貌表
v_isol  char(1);


begin
   
--shell入参赋值
--p_mv_name := '$1';
--p_tb_name := '$2';

v_total := 0;
v_limit := 1000000;

--调度时间
Select to_char(Max(EXEC_DATE),'YYYYMMDD') Into v_exec_date From etl_user.etl_process_ctrl; 
--v_exec_date := '20130101';

--找出dw表的所有列，去除特殊字段，为兼容跨库使用，这里不查找mv的列,t_data也是声明的DW表类型
Select wm_concat(column_name) Into v_column From (
Select 'm.'||column_name As  column_name From all_tab_columns Where table_name = p_tb_name And owner = 'DW_USER'
And column_name Not In ('AREA_CODE','FLAG1','FLAG2','ODS_LOADDATE','DW_LOADDATE')
Order By column_id );


--取分区、概貌表参数
Select t2.isol,t2.partition_type,t2.partition_areafiled,t2.partition_yearfiled,t2.partition_sourcetype
Into
v_isol,v_partitionType,v_partitionAreaFiled,v_partitionYearFiled,v_partitionSourceType
   From etl_user.etl_dw_ctrl t2
   Where t2.table_name = p_tb_name;

--构造分区字段和过滤条件  
  case v_partitionType
    when '0' then     --No
       v_partition:='null';
       v_where:=' where 1=1 ';
    when '1' then     --Area
       v_partition:='substr('||v_partitionAreaFiled||',0,2)';
       v_where:=' where '|| v_partitionAreaFiled ||'<> ''NULL'' and length(trim('|| v_partitionAreaFiled ||'))>1 ';
    when '2' then     --Year
       v_partition:='substr(to_char('||v_partitionYearFiled||'),0,4)';
       v_where:=' where length(to_char('|| v_partitionYearFiled ||'))>3 ';      
    when '3' then     --AreaYear
       v_partition:='substr('||v_partitionAreaFiled||',0,2) || substr(to_char('||v_partitionYearFiled||'),0,4)';
       v_where:=' where '|| v_partitionAreaFiled ||'<> ''NULL'' and '|| v_partitionYearFiled ||'<> 4504 and length(trim('|| v_partitionAreaFiled ||'))>1 and length(to_char('|| v_partitionYearFiled ||'))>3';
  end case;
 
--特殊处理，业务字段过滤
   IF p_tb_name = 'DW_IPS_ZB_TBMX_HZJS_GD_LS' THEN
    v_where:=v_where||' And m.hzzbbz=1 ';
   END IF;

--是否需要连接jgxx
if v_partitionSourceType = '1' then  --self
    v_sql := 'select '||v_column||','|| --业务字段，分区字段，控制字段
    v_partition||',
        Null,
        Null,
        SYSDATE,
                Sysdate         
        from ods_user.'||p_mv_name||' m '||v_where;
Else
    v_sql := 'select '||v_column||','|| --业务字段，分区字段，控制字段
    v_partition||',
        Null,
        Null,
        SYSDATE,
                Sysdate         
        from ods_user.'||p_mv_name||' m inner join ods_user.mv_sys_jgxx n on m.jgbm=n.jgbm'||v_where;
End If;
   
--清理日志
Delete etl_user.log_dw_table Where Exec_Date = to_date(v_exec_date,'YYYYMMDD') And mv_name = p_mv_name;
Insert Into etl_user.log_dw_table(exec_date,start_date,table_name,mv_name,is_success,err_msg,full_data,flag)
  values(to_date(v_exec_date,'YYYYMMDD'),Sysdate,p_tb_name,p_mv_name,2,'wait','full','1');
Commit;


  Open v_cur For v_sql;

  Loop


    fetch v_cur bulk Collect into t_data limit v_limit;

exit when t_data.Count=0;--退出Loop，不能使用exit when v_cur%notfound;否则取不到limit的余数

--shell入参 $2 替换
forall i in 1 .. t_data.Count insert Into TAB_DW_HS_BDZLXX_LS values t_data(i);

v_total := v_total + t_data.Count;
   
Update etl_user.log_dw_table Set end_date = Sysdate ,err_msg='OK-'||v_total
 Where Exec_Date = to_date(v_exec_date,'YYYYMMDD') And mv_name = p_mv_name;

Commit;

  end loop;


Close v_cur;


Update etl_user.log_dw_table Set end_date = Sysdate ,err_msg='OK-'||v_total,is_success = 1
Where Exec_Date = to_date(v_exec_date,'YYYYMMDD') And mv_name = p_mv_name;
Commit;




Exception


   when others then
   rollback;

v_errmsg := '失败，错误信息超长，请检查程序';
Update etl_user.log_dw_table Set end_date = Sysdate ,err_msg=v_errmsg,is_success = -1
Where Exec_Date = to_date(v_exec_date,'YYYYMMDD') And mv_name = p_mv_name;
Commit;

--允许此句异常
v_errmsg := Sqlerrm;
Update etl_user.log_dw_table Set end_date = Sysdate ,err_msg=v_errmsg,is_success = -1
Where Exec_Date = to_date(v_exec_date,'YYYYMMDD') And mv_name = p_mv_name;
Commit;

End SP_ODS2DW_TEST;