通過外部表改進一個繁瑣的大查詢

今天處理了一個比較有意思的案例，說是有意思，因為涉及多個部門，但是哪個部門似乎都不願意接。最後還是用了一些巧力，化干戈為玉帛。
問題的背景是這樣的，業務部門需要做一個大查詢，他們目前只拿到了部分賬號的一個id欄位的值，需要匹配得到一個類似手機號的欄位值，開發部門提供了對應的sql語句，會關聯兩張表來匹配得到對的資料，然後反饋到DBA這裡的時候就是最終的sql語句了，DBA查詢得到資料，然後反饋給業務部門。大體的流程是這樣的。
   但是現在的問題是，業務部門需要提供的id有60多萬個，開發部門看到這個情況就不太願意提供這樣的語句了，你說一條一條提供吧，可能對於他們來說還需要不少的工作量，而且檔案可能幾十M,工作量也非常大。對於業務部門來說，他們又不懂技術細節，對於DBA來說，巧婦難為無米之炊。所以這個時候就有些踢皮球了的感覺了。
   我看了下，覺得這活畢竟也不是經常有這種問題，那就接了吧。雖然中途碰到了不少的小問題，不過也著實值得一試。
    開發部門提供的語句類似下面的形式。
select CID from test_user_info  where login_name='?'
select SECURITY_PHONE from test_user_certification_info  where cid='?'
    業務部門就提供了一個excel檔案，裡面是60多萬的id值，想直接轉到linux環境裡還不行。
    從我的角度來看，大體有這些考慮，也算是問題的一些難點吧。
     (1)這些id值怎麼通過excel傳輸到內網環境，對於內網而言，大檔案的傳輸目前有x M的限制
     (2)因為涉及的id還是有些多，那麼這類操作只能備庫操作了。
     (3)in 的限制,如果根據提供的id來匹配，那麼語句select CID from test_user_info  where login_name='?' 是不可避免要使用in的方式了。但是in的方式會有1000個以內列舉值的限制，對於60萬的id值來說，如果這麼切分，工作量和難度又會加大。所以in的方式還是不太好。
     (4)等我連線到環境，發現問題比我想的還要難一些，這兩個表 test_user_info，test_user_certification_info目前做了拆分，把資料拆成了12份。意味值目前存在12個使用者平均儲存了這些資料。
對於這個問題的處理，這個時候就不單單是友情支援了，還是需要好好考慮一下，怎麼巧妙解決，而不是光靠苦力了。
我們來逐個分析這個問題。
第一個怎麼把excel裡面的60萬id拷貝到內網環境，這個花了我一些時間去琢磨，首先這個excel有近15M,直接拷貝不了，而且還有網路的流量限制。而且就算把excel檔案拷貝過去，在linux下也直接解析不了。所以我是通過excel把id列的值拷貝到文字檔案中，然後通過雲伺服器來中轉這個檔案，避開了流量的限制。間接實現了首要條件。
第二個是目前涉及的id有些多，只能在備庫執行，這個倒沒有異議，但是結合第三條來看，需要避免使用in list的方式，我們可以採用臨時表的方式，或者使用外部表。
所以對此我打算在主庫中建立外部表，然後外部表的ddl會同步到備庫，然後把實際的文字檔案拷貝到備庫去，查詢操作都在備庫執行。這樣就和主庫沒有了關係。備庫怎麼查詢主庫都不會收到影響。
所以我在主庫做了如下的操作。
首先建立目錄。
SQL> create directory ext_dp_dir as '/home/oracle/backup_stage';
Directory created.
然後建立外部表
CREATE TABLE  test_cn
      (cn    varchar2(50)
       )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_dp_dir
      ACCESS PARAMETERS
        (
        RECORDS DELIMITED BY NEWLINE      
        )
      LOCATION ('test_cn.txt')
     );
其中test_cn.txt就是最開始所說的文字檔案，在主庫中執行，在備庫中驗證。
SQL> @a.sql
Table created.
備庫中驗證,發現已經能夠正常識別了。
SQL> select count(*)from test_cn;
  COUNT(*)
----------
    608816
然後說說第4個問題，對這個表了拆分，怎麼查詢好一些。
開發提供的語句如下。
select CID from test_user_info  where login_name='?'
select SECURITY_PHONE from test_user_certification_info  where cid='?'
我們在這個基礎上改進,把表的關聯糅合起來，輸出完整的欄位匹配來，到時候提供一個完整的列表，不需要再刪除也不怕。
語句如下：
select t2.SECURITY_PHONE,t2.CID from
(
select login_name,cid from acc1.test_USER_INFO
 ) t1,
(
select security_phone,cid  from acc1.test_USER_CERTIFICATION_INFO
)
 t2,TESTDBA.xianjian_cn t3
 where t1.login_name=t3.cn
       and t1.cid=t2.cid;  
但這個語句的缺點是隻是其中的一個使用者，目前有12個拆分使用者，那麼我們就包裝一下，寫個很簡單的指令碼來。
指令碼1 check_data.sh
sqlplus -s / as sysdba <<EOF
spool b.log append
select t2.SECURITY_PHONE,t2.CID from
(
select login_name,cid from $1.CYUC_USER_INFO
 ) t1,
(
select security_phone,cid  from $1.CYUC_USER_CERTIFICATION_INFO
)
 t2,CYDBA.xianjian_cn t3
 where t1.login_name=t3.cn
       and t1.cid=t2.cid;
spool off
EOF

第二個指令碼 check_all.sh的內容
sh check_data.sh ACC00
sh check_data.sh ACC02
sh check_data.sh ACC04
sh check_data.sh ACC11
sh check_data.sh ACC13
sh check_data.sh ACC15
sh check_data.sh ACC20
sh check_data.sh ACC22
sh check_data.sh ACC24
sh check_data.sh ACC31
sh check_data.sh ACC33
sh check_data.sh ACC35
逐個擊破，對比一個大sql的效率要高了很多。
不到一分鐘就查完了。當然開始還是碰到了一個小問題，那就是亂碼問題，因為我們的檔案是從windows傳過來的。開始匹配的時候發現沒有任何資料。
SQL> select '>'||cn||'<' from testdba.test_cn where rownum<20;
'>'||CN||'<'
--------------------------------------------------------------------------------
<FA7E3EF3A73E61F4F61561464C79FA7D
<586383418
<609848108
進一步分析，發現是格式的問題。
cat -v xianjian_cn.txt |less
FA7E3EF3A73E61F4F61561464C79FA7D^M
586383418^M
609848108^M
使用dos2unix格式化即可。
$dos2unix xianjian_cn.txt
dos2unix: converting file test_cn.txt to UNIX format ...
再次匹配就可以順利得到結果了。
這種處理也可以作為一種處理大批量資料查詢的一種思路，其實就是比較輕便，如果是一個常規需求，經常會有這類的查詢，我們只需要替換這個文字檔案即可，其它的部分可以設定成檢視之類的，這些功能點就固化起來了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2051574/，如需轉載，請註明出處，否則將追究法律責任。