ROWNUM固化外部表结果集存在问题 [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3715 | 回复: 0   主题: ROWNUM固化外部表结果集存在问题 [转帖]        下一篇 
wayne
注册用户
等级:中校
经验:1690
发帖:221
精华:0
注册:2011-7-21
状态:离线
发送短消息息给wayne 加好友    发送短消息息给wayne 发消息
发表于: IP:您无权察看 2011-9-8 21:05:07 | [全部帖] [楼主帖] 楼主

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




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论