海量資料遷移之外部表切分

在前幾篇中討論過海量資料的並行載入，基本思路就是針對每一個物理表都會有一個對應的外部表，在做資料遷移的時候，如果表有上百G的時候，一個物理表對應一個外部表效能上會沒有任何提升。如果需要做資料插入的時候，對undo是極大的挑戰，從某種程度上而言，效能應該要比datapump要差。這個時候可以考慮一個物理表對應多個外部表，比如一個表有100G。可以考慮生成100個external dump 檔案，然後載入生成100個外部表，每個dump檔案對應一個外部表，這樣做資料的插入的時候就相對容易控制了。每一個外部表的資料載入到目標庫之後，commit一次，就能及時的釋放Undo資源，提高效能。

比如表T生成了兩個dump檔案（t_1.dmp,t_2.dmp)，就可以考慮如下的方式來載入，黃色部分是對應的dump檔案。

CREATE TABLE T_EXT_1
   (    id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "EXPDP_LOCATION"
      LOCATION
       ( 't_1.dmp'
       )
    );

CREATE TABLE T_EXT_2
   (    id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "EXPDP_LOCATION"
      LOCATION
       ( 't_2.dmp'
       )
    );
對應的指令碼如下：
其中在DUMP目錄下存放著生成的dump檔案，根據動態匹配得到最終生成了幾個dump檔案，來決定建立幾個對應的外部表。

target_owner=`echo "$2" |awk -F@ '{print $1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'`
source_owner=`echo "$1" |awk -F@ '{print $1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'`
tab_name=`echo "$3"|tr '[a-z]' '[A-Z]'`
owner_account=$5

tmp_parallel=`ls -l ../DUMP/${tab_name}_[0-9]*.dmp|wc -l`
echo  parallel :$tmp_parallel
for i in {1..$tmp_parallel};
do
echo \'${tab_name}_$i.dmp\' >> tmp_${tab_name}_par_dmp.lst
done


sed -e '/^$/d'  tmp_${tab_name}_par_dmp.lst > ../DUMP_LIST/${tab_name}_par_dmp.lst
rm tmp_${tab_name}_par_dmp.lst
dump_list=`cat ../DUMP_LIST/${tab_name}_par_dmp.lst`


print "
conn  $1
set feedback off
set linesize 100
col data_type format a30
set pages 0
set termout off
SELECT 
        t1.COLUMN_NAME,  
        t1.DATA_TYPE  
        || DECODE (  
             t1.DATA_TYPE,  
              'NUMBER', DECODE (  
                              '('  
                           || NVL (TO_CHAR (t1.DATA_PRECISION), '*')  
                           || ','  
                           || NVL (TO_CHAR (t1.DATA_SCALE), '*')  
                           || ')',  
                           '(*,*)', NULL,  
                           '(*,0)', '(38)',  
                              '('  
                           || NVL (TO_CHAR (t1.DATA_PRECISION), '*')  
                           || ','  
                           || NVL (TO_CHAR (t1.DATA_SCALE), '*')  
                           || ')'),  
              'FLOAT', '(' || t1.DATA_PRECISION || ')',  
              'DATE', NULL,  
              'TIMESTAMP(6)', NULL,  
              '(' || t1.DATA_LENGTH || ')')  ||','
           AS DATA_TYPE
           from all_tab_columns t1 where owner=upper('$owner_account') AND table_name=upper('$3' )
order by t1.column_id;
"|sqlplus -s /nolog > ${tab_name}.temp


sed -e '/^$/d' -e '$s/.$//' -e  's/CLOB(4000)/CLOB/g' -e  's/BLOB(4000)/BLOB/g' ${tab_name}.temp > ../DESC_LIST/${tab_name}.desc
rm ${tab_name}.temp
for i in {1..$tmp_parallel}
do 
echo loading table ${tab_name} as ${tab_name}_EXT_$i
sqlplus -s $2 < set timing on
set echo on
CREATE TABLE  ${tab_name}_EXT_$i
   ( 
  `cat ../DESC_LIST/${tab_name}.desc `
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY $4
      LOCATION(
  `sed -n "${i}p"  ../DUMP_LIST/${tab_name}_par_dmp.lst`
    ));
EOF
done
exit

生成的日誌類似下面的格式：
 loading table T as T_EXT_1
Elapsed: 00:00:01.33
 loading table T as T_EXT_2
Elapsed: 00:00:01.30