是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