转载

通过外部表改进一个繁琐的大查询

今天处理了一个比较有意思的案例，说是有意思，因为涉及多个部门，但是哪个部门似乎都不愿意接。最后还是用了一些巧力，化干戈为玉帛。
问题的背景是这样的，业务部门需要做一个大查询，他们目前只拿到了部分账号的一个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 ...
再次匹配就可以顺利得到结果了。
这种处理也可以作为一种处理大批量数据查询的一种思路，其实就是比较轻便，如果是一个常规需求，经常会有这类的查询，我们只需要替换这个文本文件即可，其它的部分可以设置成视图之类的，这些功能点就固化起来了。

正文到此结束