今天處理了一個比較有意思的案例，說是有意思，因?yàn)樯婕岸鄠€部門，但是哪個部門似乎都不愿意接。最后還是用了一些巧力，化干戈為玉帛。
問題的背景是這樣的，業(yè)務(wù)部門需要做一個大查詢，他們目前只拿到了部分賬號的一個id字段的值，需要匹配得到一個類似手機(jī)號的字段值，開發(fā)部門提供了對應(yīng)的sql語句，會關(guān)聯(lián)兩張表來匹配得到對的數(shù)據(jù)，然后反饋到DBA這里的時候就是最終的sql語句了，DBA查詢得到數(shù)據(jù)，然后反饋給業(yè)務(wù)部門。大體的流程是這樣的。
?? 但是現(xiàn)在的問題是，業(yè)務(wù)部門需要提供的id有60多萬個，開發(fā)部門看到這個情況就不太愿意提供這樣的語句了，你說一條一條提供吧，可能對于他們來說還需要不少的工作量，而且文件可能幾十M,工作量也非常大。對于業(yè)務(wù)部門來說，他們又不懂技術(shù)細(xì)節(jié)，對于DBA來說，巧婦難為無米之炊。所以這個時候就有些踢皮球了的感覺了。
?? 我看了下，覺得這活畢竟也不是經(jīng)常有這種問題，那就接了吧。雖然中途碰到了不少的小問題，不過也著實(shí)值得一試。
??? 開發(fā)部門提供的語句類似下面的形式。
select CID from test_user_info? where login_name='?'
select SECURITY_PHONE from test_user_certification_info? where cid='?'
??? 業(yè)務(wù)部門就提供了一個excel文件，里面是60多萬的id值，想直接轉(zhuǎn)到linux環(huán)境里還不行。
??? 從我的角度來看，大體有這些考慮，也算是問題的一些難點(diǎn)吧。
???? (1)這些id值怎么通過excel傳輸?shù)絻?nèi)網(wǎng)環(huán)境，對于內(nèi)網(wǎng)而言，大文件的傳輸目前有x M的限制
???? (2)因?yàn)樯婕暗膇d還是有些多，那么這類操作只能備庫操作了。
???? (3)in 的限制,如果根據(jù)提供的id來匹配，那么語句select CID from test_user_info? where login_name='?' 是不可避免要使用in的方式了。但是in的方式會有1000個以內(nèi)枚舉值的限制，對于60萬的id值來說，如果這么切分，工作量和難度又會加大。所以in的方式還是不太好。
? ?? (4)等我連接到環(huán)境，發(fā)現(xiàn)問題比我想的還要難一些，這兩個表 test_user_info，test_user_certification_info目前做了拆分，把數(shù)據(jù)拆成了12份。意味值目前存在12個用戶平均存儲了這些數(shù)據(jù)。
對于這個問題的處理，這個時候就不單單是友情支持了，還是需要好好考慮一下，怎么巧妙解決，而不是光靠苦力了。
我們來逐個分析這個問題。
第一個怎么把excel里面的60萬id拷貝到內(nèi)網(wǎng)環(huán)境，這個花了我一些時間去琢磨，首先這個excel有近15M,直接拷貝不了，而且還有網(wǎng)絡(luò)的流量限制。而且就算把excel文件拷貝過去，在linux下也直接解析不了。所以我是通過excel把id列的值拷貝到文本文件中，然后通過云服務(wù)器來中轉(zhuǎn)這個文件，避開了流量的限制。間接實(shí)現(xiàn)了首要條件。
第二個是目前涉及的id有些多，只能在備庫執(zhí)行，這個倒沒有異議，但是結(jié)合第三條來看，需要避免使用in list的方式，我們可以采用臨時表的方式，或者使用外部表。
所以對此我打算在主庫中創(chuàng)建外部表，然后外部表的ddl會同步到備庫，然后把實(shí)際的文本文件拷貝到備庫去，查詢操作都在備庫執(zhí)行。這樣就和主庫沒有了關(guān)系。備庫怎么查詢主庫都不會收到影響。
所以我在主庫做了如下的操作。
首先創(chuàng)建目錄。
SQL> create directory ext_dp_dir as '/home/oracle/backup_stage';
Directory created.
然后創(chuàng)建外部表
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就是最開始所說的文本文件，在主庫中執(zhí)行，在備庫中驗(yàn)證。
SQL> @a.sql
Table created.
備庫中驗(yàn)證,發(fā)現(xiàn)已經(jīng)能夠正常識別了。
SQL> select count(*)from test_cn;
? COUNT(*)
----------
??? 608816
然后說說第4個問題，對這個表了拆分，怎么查詢好一些。
開發(fā)提供的語句如下。
select CID from test_user_info? where login_name='?'
select SECURITY_PHONE from test_user_certification_info? where cid='?'
我們在這個基礎(chǔ)上改進(jìn),把表的關(guān)聯(lián)糅合起來，輸出完整的字段匹配來，到時候提供一個完整的列表，不需要再刪除也不怕。
語句如下：
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;??
但這個語句的缺點(diǎn)是只是其中的一個用戶，目前有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的內(nèi)容
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的效率要高了很多。
不到一分鐘就查完了。當(dāng)然開始還是碰到了一個小問題，那就是亂碼問題，因?yàn)槲覀兊奈募菑膚indows傳過來的。開始匹配的時候發(fā)現(xiàn)沒有任何數(shù)據(jù)。
SQL> select '>'||cn||'<' from testdba.test_cn where rownum<20;
'>'||CN||'<'
--------------------------------------------------------------------------------
<FA7E3EF3A73E61F4F61561464C79FA7D
<586383418
<609848108
進(jìn)一步分析，發(fā)現(xiàn)是格式的問題。
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 ...
再次匹配就可以順利得到結(jié)果了。
這種處理也可以作為一種處理大批量數(shù)據(jù)查詢的一種思路，其實(shí)就是比較輕便，如果是一個常規(guī)需求，經(jīng)常會有這類的查詢，我們只需要替換這個文本文件即可，其它的部分可以設(shè)置成視圖之類的，這些功能點(diǎn)就固化起來了。
《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的通过外部表改进一个繁琐的大查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。