在客户的11.2.0.2环境中碰到了这个问题，Oracle在处理包含ROWNUM固化的外部表加载数据时返回错误的结果。
外部表构造描述可以参考：利用外部表读取告警日志文件
客户环境中创建的外部表和上面链接中的例子几乎完全一致：
SQL> CREATE TABLE T_ALERT 2 (TEXT VARCHAR2(4000) 3 ) 4 ORGANIZATION EXTERNAL 5 (TYPE ORACLE_LOADER 6 DEFAULT DIRECTORY D_ALERT 7 ACCESS PARAMETERS 8 (RECORDS DELIMITED BY NEWLINE 9 FIELDS (TEXT (1:255) CHAR)) 10 LOCATION ('alert_xshdb1.log')); TABLE created. SQL> SELECT * FROM T_ALERT WHERE ROWNUM < 10; TEXT ------------------------------------------------------------------------------------------ Tue Nov 16 15:32:47 2010 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Private Interface 'eth2:1' configured FROM GPnP FOR USE AS a private interconnect. [name='eth2:1', TYPE=1, ip=169.254.88.96, mac=00-15-17-f8-aa-a2, net=169.254.0.0/16, mask=255.255.0.0, USE=haip:cluster_interconnect/62] Public Interface 'eth0' configured FROM GPnP FOR USE AS a public interface. [name='eth0', TYPE=1, ip=10.0.10.53, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1] Public Interface 'eth0:1' configured FROM GPnP FOR USE AS a public interface. 9 ROWS selected. |
外部表访问正常，但是下面这两个等价的写法，得到的结果却大相径庭：
SQL> SELECT COUNT(*) FROM T_ALERT; COUNT(*) ---------- 158299 SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT) 2 SELECT * FROM A B 3 WHERE B.RN >= (SELECT C.RN FROM A C WHERE TEXT = 'Mon Aug 22 02:00:00 2011'); RN TEXT ---------- -------------------------------------------------------------------------------- 158300 Starting ORACLE instance (normal) 158301 LICENSE_MAX_SESSION = 0 158302 LICENSE_SESSIONS_WARNING = 0 158303 Private Interface 'eth2:1' configured FROM GPnP FOR USE AS a private interconnect. 158304 [name='eth2:1', TYPE=1, ip=169.254.88.96, mac=00-15-17-f8-aa-a2, net=169.254.0.0/16, mask=255.255.0.0, USE=haip:cluster_interconnect/62] 158305 Public Interface 'eth0' configured FROM GPnP FOR USE AS a public interface. 158306 [name='eth0', TYPE=1, ip=10.0.10.53, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1] 158307 Public Interface 'eth0:1' configured FROM GPnP FOR USE AS a public interface. 158308 [name='eth0:1', TYPE=1, ip=10.0.10.55, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1] 158309 Public Interface 'eth0:2' configured FROM GPnP FOR USE AS a public interface. 158310 [name='eth0:2', TYPE=1, ip=10.0.10.57, mac=00-21-5e-11-7d-c0, net=10.0.10.0/24, mask=255.255.255.0, USE=public/1] 158311 Shared memory segment FOR instance monitoring created 158312 Picked latch-free SCN scheme 3 . . . 316587 Sun Aug 21 06:00:00 2011 316588 VKRM started WITH pid=120, OS id=32339 316589 Mon Aug 22 02:00:00 2011 316590 Closing Resource Manager plan via scheduler window 316591 Clearing Resource Manager plan via parameter 316592 Mon Aug 22 02:00:35 2011 316593 Thread 1 advanced TO log SEQUENCE 8130 (LGWR switch) 316594 CURRENT log# 2 seq# 8130 mem# 0: +DATADG/xshdb/onlinelog/group_2.258.735233587 316595 CURRENT log# 2 seq# 8130 mem# 1: +FRADG/xshdb/onlinelog/group_2.258.735233587 316596 Mon Aug 22 02:00:36 2011 316597 Archived Log entry 10625 added FOR thread 1 SEQUENCE 8129 ID 0x287369ad dest 1: 158298 ROWS selected. SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT) 2 SELECT B.* FROM A B, A C 3 WHERE B.RN >= C.RN 4 AND C.TEXT = 'Mon Aug 22 02:00:00 2011'; RN TEXT ---------- ------------------------------------------------------------------------------- 158299 Archived Log entry 10625 added FOR thread 1 SEQUENCE 8129 ID 0x287369ad dest 1: 158298 Mon Aug 22 02:00:36 2011 158297 CURRENT log# 2 seq# 8130 mem# 1: +FRADG/xshdb/onlinelog/group_2.258.735233587 158296 CURRENT log# 2 seq# 8130 mem# 0: +DATADG/xshdb/onlinelog/group_2.258.735233587 158295 Thread 1 advanced TO log SEQUENCE 8130 (LGWR switch) 158294 Mon Aug 22 02:00:35 2011 158293 Clearing Resource Manager plan via parameter 158292 Closing Resource Manager plan via scheduler window 158291 Mon Aug 22 02:00:00 2011、 9 ROWS selected. |
第二个SQL语句的结果是我们所期望的，而第一个SQL的结果明显是错误的，对比范围结果不难发现，除了第一条记录之外，语句返回了alert日志文件中其他所有的数据，只不过ROWNUM的结果到是符合要求。
检查两个SQL分别的执行计划：
SQL> EXPLAIN PLAN FOR 2 WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT) 3 SELECT * FROM A B 4 WHERE B.RN >= (SELECT C.RN FROM A C WHERE TEXT = 'Mon Aug 22 02:00:00 2011'); Explained. SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Plan hash VALUE: 2687076668 ----------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 15M| 58 (0)| 00:00:01 | |* 1 | VIEW | | 8168 | 15M| 29 (0)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | EXTERNAL TABLE ACCESS FULL | T_ALERT | 8168 | 15M| 29 (0)| 00:00:01 | |* 4 | VIEW | | 8168 | 15M| 29 (0)| 00:00:01 | | 5 | COUNT | | | | | | | 6 | EXTERNAL TABLE ACCESS FULL| T_ALERT | 8168 | 15M| 29 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - FILTER("B"."RN">= (SELECT "C"."RN" FROM (SELECT ROWNUM "RN","TEXT" "TEXT" FROM "T_ALERT" "T_ALERT") "C" WHERE "TEXT"='Mon Aug 22 02:00:00 2011')) 4 - FILTER("TEXT"='Mon Aug 22 02:00:00 2011') 20 ROWS selected. SQL> EXPLAIN PLAN FOR 2 WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT) 3 SELECT B.* FROM A B, A C 4 WHERE B.RN >= C.RN 5 AND C.TEXT = 'Mon Aug 22 02:00:00 2011'; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Plan hash VALUE: 883860547 ------------------------------------------------------------------------------------------- | Id | Operation |Name |ROWS |Bytes|TempSpc| Cost (%CPU)| TIME | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |3335K| 12G| | 6960 (1)| 00:01:24 | | 1 | MERGE JOIN | |3335K| 12G| | 6960 (1)| 00:01:24 | | 2 | SORT JOIN | |8168 | 15M| 31M| 3469 (1)| 00:00:42 | | 3 | VIEW | |8168 | 15M| | 29 (0)| 00:00:01 | | 4 | COUNT | | | | | | | | 5 | EXTERNAL TABLE ACCESS FULL|T_ALERT|8168 | 15M| | 29 (0)| 00:00:01 | |* 6 | SORT JOIN | |8168 | 15M| 31M| 3469 (1)| 00:00:42 | |* 7 | VIEW | |8168 | 15M| | 29 (0)| 00:00:01 | | 8 | COUNT | | | | | | | | 9 | EXTERNAL TABLE ACCESS FULL|T_ALERT|8168 | 15M| | 29 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 6 - access(INTERNAL_FUNCTION("B"."RN")>=INTERNAL_FUNCTION("C"."RN")) FILTER(INTERNAL_FUNCTION("B"."RN")>=INTERNAL_FUNCTION("C"."RN")) 7 - FILTER("C"."TEXT"='Mon Aug 22 02:00:00 2011') 23 ROWS selected. |
对于连接的写法，ORACLE采用了MERGE JOIN。而结果错误的SQL，Oracle直接通过对两个VIEW进行过滤的方式获取记录，这时第一个VIEW中的ROWNUM固化结果集并未生效，怀疑是Oracle在处理外部表时出现的bug。
Pingback: ROWNUM固化外部表结果集存在问题（二） | yangtingkun
Pingback: 外部表的ROWID信息 | yangtingkun