oracle11gr2用rownum在分页时执行计划出现异常,无结果集返回?

近期更换了数据库版本,把oracle10g替换成11g2r出现了一个问题,一些原本可以运行的分页sql数据获取没有了结果集反馈,测试后发现产生的条件是:-1,select中包含rownum-2.where条件中包含两个区间一致的查询日期条件比如AND l.be_starttime >= trunc(SYSDATE, 'yy')AND l...显示全部

近期更换了数据库版本,把oracle10g替换成11g2r
出现了一个问题,一些原本可以运行的分页sql数据获取没有了结果集反馈,测试后发现产生的条件是:
-1,select中包含rownum
-2.where条件中包含两个区间一致的查询日期条件比如
AND l.be_starttime >= trunc(SYSDATE, 'yy')
AND l.be_starttime >= to_date('2019-08-01', 'yyyy-mm-dd')
两个条件同时存在时会在执行计划中出现一个   2 - filter(NULL IS NOT NULL)
具体实例sql:

SELECT l.activityarr,ROWNUM
          FROM crm_lead l
         WHERE l.isactive = 'Y'
           AND l.ad_org_id = 1000000
           AND l.docstatus = 'I'
           AND l.be_starttime >= trunc(SYSDATE, 'yy')
           AND l.be_starttime >= to_date('2019-08-01', 'yyyy-mm-dd');

该sql的执行计划:

Plan hash value: 2923278655
 
--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    18 |     0   (0)|          |       |       |
|   1 |  COUNT                |          |       |       |            |          |       |       |
|*  2 |   FILTER              |          |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ALL|          |  3887 | 69966 |  7956   (1)| 00:01:36 |     1 |     4 |
|*  4 |     TABLE ACCESS FULL | CRM_LEAD |  3887 | 69966 |  7956   (1)| 00:01:36 |     1 |     4 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(NULL IS NOT NULL)
   4 - filter("L"."DOCSTATUS"=U'I' AND "L"."AD_ORG_ID"=1000000 AND "L"."ISACTIVE"='Y')

求助这个问题能怎么处理?具体产生原因是什么呢?

收起
参与7

查看其它 1 个回答renou2012的回答

renou2012renou2012  数据库管理员 , KE

你的意思是ROWNUM 是否存在 影响了结果集?
SELECT l.activityarr

      FROM crm_lead l
     WHERE l.isactive = 'Y'
       AND l.ad_org_id = 1000000
       AND l.docstatus = 'I'
       AND l.be_starttime >= trunc(SYSDATE, 'yy')
       AND l.be_starttime >= to_date('2019-08-01', 'yyyy-mm-dd');
       

SELECT l.activityarr,ROWNUM

      FROM crm_lead l
     WHERE l.isactive = 'Y'
       AND l.ad_org_id = 1000000
       AND l.docstatus = 'I'
       AND l.be_starttime >= trunc(SYSDATE, 'yy')
       AND l.be_starttime >= to_date('2019-08-01', 'yyyy-mm-dd');
金融其它 · 2019-08-08
浏览1614
  • 是rownum的存在影响了结果集,如果没有rownum执行计划就没有null is not null; 没有rownum的情况下执行计划是: Plan hash value: 3022366153 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 872 | 15696 | 7956 (1)| 00:01:36 | | | | 1 | PARTITION RANGE ALL| | 872 | 15696 | 7956 (1)| 00:01:36 | 1 | 4 | |* 2 | TABLE ACCESS FULL | CRM_LEAD | 872 | 15696 | 7956 (1)| 00:01:36 | 1 | 4 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("L"."DOCSTATUS"=U'I' AND "L"."AD_ORG_ID"=1000000 AND "L"."ISACTIVE"='Y' AND "L"."BE_STARTTIME">=GREATEST(TRUNC(SYSDATE@!,'fmyy'),TO_DATE(' 2019-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) ------------------------------------------------------------------------------------------------ 有rownum执行计划就成了 Plan hash value: 2923278655 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 0 (0)| | | | | 1 | COUNT | | | | | | | | |* 2 | FILTER | | | | | | | | | 3 | PARTITION RANGE ALL| | 3887 | 69966 | 7956 (1)| 00:01:36 | 1 | 4 | |* 4 | TABLE ACCESS FULL | CRM_LEAD | 3887 | 69966 | 7956 (1)| 00:01:36 | 1 | 4 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NULL IS NOT NULL) 4 - filter("L"."DOCSTATUS"=U'I' AND "L"."AD_ORG_ID"=1000000 AND "L"."ISACTIVE"='Y') --------------------------------------------------------------------------------------------------
    2019-08-08

回答者

renou2012
数据库管理员KE

renou2012 最近回答过的问题

回答状态

  • 发布时间:2019-08-08
  • 关注会员:2 人
  • 回答浏览:1614
  • X社区推广