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

jeanron100發表於2016-03-08
今天處理了一個比較有意思的案例,說是有意思,因為涉及多個部門,但是哪個部門似乎都不願意接。最後還是用了一些巧力,化干戈為玉帛。
問題的背景是這樣的,業務部門需要做一個大查詢,他們目前只拿到了部分賬號的一個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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章