在客户的11.2.0.2环境中碰到了这个问题，Oracle在处理包含ROWNUM固化的外部表加载数据时返回错误的结果。
外部表构造描述可以参考：http://yangtingkun.net/?p=35
客户环境中创建的外部表和上面链接中的例子几乎完全一致：
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。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-9-8 21:05:07 |
赞(
操作
