今天处理开发同学提交的一个数据查询需求，看起来是一个很常规的SQL,但是有一点不同的是，他们提供了两份文件，一份是一个id列表，大概有3000多个id值，另外一个份是个SQL文件。

   之前也处理过几十万，上百万id值的情况，使得我原来开发中对于变动的敏感性依旧存在，所以我采用了另外一种灵活的方式，即外部表,外部表是数据库外的数据存在，在数据库依旧可以读取访问。

CREATE TABLE  test_cn

      (cn    varchar2(50)        )     ORGANIZATION EXTERNAL       (TYPE ORACLE_LOADER       DEFAULT DIRECTORY ext_dir       ACCESS PARAMETERS         (         RECORDS DELIMITED BY NEWLINE               )       LOCATION ('data.txt')      );   而在这个基础上运行的SQL语句也很简短。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;   这样一来就达到了一种一劳永逸的效果，那就是后期如果开发同学继续提供另外一个查询，只要提供了id值，不管是多大，我都能轻松处理，不管是哪个业务的SQL我都能灵活套用。

   但是问题来了，上面的SQL语句执行的时候，速度让我很不满意，因为持续了近2分钟。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;

Elapsed: 00:01:59.39为什么很不满意，是因为这个“表”中的主键是基于字段uin的，竟然查询速度这么慢，实在不给面子。

INDEX_NAME                     COLUMN_NAME  INDEX_TYPE  UNIQUENES

------------------------------ ------------ --------------------- PK_USER_CERTIFICATION_INFO1    UIN          NORMAL      UNIQUE对于这类问题我还是有不小的兴趣，毕竟能够顺手优化优化也是不错的体验。我尝试加了rownum，尽管这样不够严谨，但是输出结果和时间还是和开始的差不多。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) and rownum<=4000 ;

Elapsed: 00:01:59.15可见Oracle优化器早就看穿了我的心思，我怎么能够耍点小聪明呢。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn where rownum<=3200) ;

Elapsed: 00:00:00.29这样一个查询就能够达到非一般的速度。

这是为什么呢。要想得到一些更为细致的问题，那我们就开启trace来诊断一下，怎么诊断呢，一种比较自然的思路那就是10053事件。

10053事件诊断SQL

开启10053事件的步骤如下：

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

explain plan for select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ; ALTER SESSION SET EVENTS '10053 trace name context off';其中能够看到不少细节的信息，我摘取出一小段来。

FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)

FPD:  Current where clause predicates "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN") try to generate transitive predicate from check constraints for query block SEL$1 (#0) finally: "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN") 最后经过查询转换，得到的最终语句如下：

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "TEST_CERTIFICATION_INFO"."UIN" "UIN",..... FROM "TEST"."TEST_CN" "TEST_CN", ( (SELECT "ACC00_TEST_CERTIFICATION_INFO"."UIN" ... "ACC35_TEST_CERTIFICATION_INFO")) "TEST_CERTIFICATION_INFO" WHERE "TEST_CERTIFICATION_INFO"."UIN"=TO_NUMBER("TEST_CN"."CN") kkoqbc: optimizing query block SEL$2 (#14)   可能看到这里就有些懵了，这个test_certification_info其实是个视图，里面包含有12个物化视图。其实这个简单的查询就好比是12个物化视图和一个外部表的关联查询。

   那么为什么子查询使用了rownum之后，效率大大提升呢。这个可以从日志中看出端倪，我们可以清楚的看到优化器预估的时候这个外部表的数据条数是179950，和现在的3000多条想去甚远。

Table Stats::

  Table: TEST_CN  Alias: TEST_CN     #Rows: 179950  #Blks:  462  AvgRowLen:  21.00  ChainCnt:  0.00 Access path analysis for TEST_CN那么为什么优化器认为是179950条数据呢，这个和统计信息还是密切相关，尽管外部表不占用数据文件的存储，但是依然还是有一个基本的统计信息。

SQL> select num_rows from dba_tables where table_name='TEST_CN';

  NUM_ROWS ----------     179950可能有很多同学说，那就收集统计信息，应该能够解决这个问题。SQL>  exec dbms_stats.gather_table_stats(ownname=>'TEST',TABNAME=>'TEST_CN'); PL/SQL procedure successfully completed.   然后再次尝试，竟然还是很慢，查看执行计划发现里面始终是走了全表扫描。

   这个问题的一种快速解决方式就是使用子查询中的rownum来限定，如果查询的数据缺失够多，走全表也不失为一种合理的方法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-2135033/，如需转载，请注明出处，否则将追究法律责任。