ROWNUM固化外部表结果集存在问题

在客户的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 20119 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。

This entry was posted in BUG and tagged , , . Bookmark the permalink.

2 Responses to ROWNUM固化外部表结果集存在问题

  1. Pingback: ROWNUM固化外部表结果集存在问题(二) | yangtingkun

  2. Pingback: 外部表的ROWID信息 | yangtingkun

Leave a Reply

Your email address will not be published. Required fields are marked *