客户环境中创建的外部表和上面链接中的例子几乎完全一致：

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。