注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

陈钟鸣的博客

独立之精神,自由之思想.

 
 
 

日志

 
 

oracle 10.2.0.1.0 关于rownum的一个bug  

2008-12-11 09:23:08|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
今天一个项目组使用rownum进行分页时,出现问题。查了asktom知道是oracle的一个bug。
create table t1 (
t1pk   number,
t1char  varchar2(10)
)

insert into t1 values (1,'T1ROW1');
insert into t1 values (2,'T1ROW2');
insert into t1 values (3,'T1ROW3');

create table t2 (
t2pk  number,
t2fk  number,  
t2num number,
t2date  date
)

insert into t2 values (10,1,111,to_date('1-nov-2008'));
insert into t2 values (20,2,222,to_date('2-nov-2008'));
insert into t2 values (30,3,333,to_date('3-nov-2008'));

CREATE OR REPLACE
FUNCTION is_param_passed(p_param NUMBER)
    RETURN integer
AS
BEGIN
  IF p_param IS NULL
  THEN
    RETURN 0;
  ELSE
    RETURN 1;
  END IF;
END is_param_passed;

Here is the query:

SELECT 
    ROWNUM AS rowcounter,
    recs.*
FROM   (
            SELECT *
            FROM   t1
            WHERE  t1pk IN (
                SELECT DISTINCT t2fk
                FROM   t2
                WHERE   t2num IN (333)
                AND is_param_passed(t2.t2pk) = 1
                )
            ORDER BY t1char
        ) recs
行6でエラーが発生しました。:
ORA-00600: 内部エラー?コード、引数: [evapls1],[],[],[],[],[],[],[]
----------------------------------------------------------------

SQL> alter session set "_optimizer_filter_pred_pullup"=false;

セッションが変更されました。

SQL> SELECT
  2      ROWNUM AS rowcounter,
  3      recs.*
  4  FROM   (
  5              SELECT *
  6              FROM   t1
  7              WHERE  t1pk IN (
  8                  SELECT DISTINCT t2fk
  9                  FROM   t2
 10                  WHERE   t2num IN (333)
 11                  AND is_param_passed(t2.t2pk) = 1
 12                  )
 13              ORDER BY t1char
 14          ) recs;

ROWCOUNTER       T1PK T1CHAR
---------- ---------- --------------------
         1          3 T1ROW3

select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

--升级至10.2.0.3这个错误消失。
ASKTOM:
I have discovered that the situation I described yesterday (ROWNUM voodoo?   November 12, 2008) may 
actually be an Oracle bug.  Specifically, metalink bug 5708897.  In that metalink report they 
suggest a workaround:

alter session set "_optimizer_filter_pred_pullup"=false;

That workaround did seem to fix the problem.  Apparently this bug was fixed in 10.2.0.4.  I'm 
waiting to get a 10.2.0.4 instance up and running so I can test/confirm there.  I will continue to 
post any additional info to avoid having any of you waste time on my submission. 
  评论这张
 
阅读(221)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017