oracle blob 操作与性能备忘

declare evtid varchar2(40); --19min
counts number(5);
XString varchar2(3000);
YString varchar2(3000);
i number;
rs1 varchar2(3000);
---
type record_sgt_col is record(
sgtid varchar2(40),
cout number,
xs varchar2(3000),
ys varchar2(3000)
);
type sgt_col is table of record_sgt_col INDEX BY PLS_INTEGER;
sgt_cols sgt_col;
newsgt_cols sgt_col;
---
/*type sgtid is table of cygc_sj_sgt_cyj_test.eventid%TYPE INDEX BY PLS_INTEGER;
sgtids sgtid;*/

begin
  select *
   bulk collect into sgt_cols from sgt_cols ;--存到临时表里，提高性能
  for idx in sgt_cols.FIRST..sgt_cols.LAST loop

          counts:=nvl(sgt_cols(idx).cout,0);
          XString:=sgt_cols(idx).xs;
          YString:=sgt_cols(idx).ys;          
          -- dbms_output.put_line(counts||'-------'||sgtids(idx));            
          XString:=replace(replace(replace(trim(XString),chr(10),''),chr(13),''),' ',',');
          YString:=replace(replace(replace(trim(YString),chr(10),''),chr(13),''),' ',',');
         --dbms_output.put_line(counts);
         if counts <>0 then
            rs1:=counts||chr(10);         
            for i in 1..counts loop
              rs1:=rs1||nvl(regexp_substr(YString,'[^,]+',1,i,'i'),'0.00')||','||nvl(regexp_substr(XString,'[^,]+',1,i,'i'),'0.00')||',0.00'||chr(10);
            end loop;
            
            /*update cygc_sj_sgt_cyj_test
            set SGT=ctob(to_clob(rs1)),update_date=sysdate
             where eventid=sgt_cols(idx).sgtid ;*/--直接更新，性能太慢，采用中间临时表
             insert /*+append */ into tmp_sgt(Eventid,sgt) values(sgt_cols(idx).sgtid,rs1);
             --dbms_output.put_line(rs1);
             if mod(idx,1000)=0 then
                commit;
                dbms_output.put_line(idx||sysdate);
             end if;
          end if;
  end loop;
  commit;
 update tmp_sgt set blob_sgt=ctob(to_clob(sgt));--varchar2转成blob
  commit;
  merge into cygc_sj_sgt_cyj a
  using tmp_sgt b
  on (a.eventid=b.eventid)
  when matched then
  update 
  set a.sgt=b.blob_sgt,update_date=sysdate;
  commit;
  exception
    when others then
      dbms_output.put_line('error: ' || sqlerrm);
      rollback;
end;
---------------
create table tmp_sgt
(
Eventid varchar2(80),
sgt varchar2(3000),
blob_sgt blob
)
----------------------
select * from tmp_sgt;

update tmp_sgt set blob_sgt=ctob(to_clob(sgt));
commit;

merge into cygc_sj_sgt_cyj a
using tmp_sgt b
on (a.eventid=b.eventid)
when matched then
  update 
  set a.sgt=b.blob_sgt,update_date=sysdate;
commit;

-----  
create table sgt_cols
select eventid,
  Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(sgt,
          Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('HOLE'))-Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('ARRAY 1'))-10,
          Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('ARRAY 1'))+8
          )),
          Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(sgt,
          Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('# Y values'))-Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('# X values'))-12,
          Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('# X values'))+12
          )),
          Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(sgt,
          DBMS_LOB.GETLENGTH(sgt)-Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('set 0')),
          Dbms_Lob.Instr(sgt,Utl_Raw.Cast_To_Raw('set 0'))+7
          ))
   bulk collect into sgt_cols from cygc_sj_sgt_cyj where rkdw like '%三厂%';