oracle11gr2用rownum在分页时执行计划出现异常,无结果集返回？
近期更换了数据库版本,把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')求助这个问题能怎么处理?具体产生原因是什么呢?
2同行回答
你的意思是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');
- 是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') --------------------------------------------------------------------------------------------------