NULL IS NOT NULL in execution plan
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2010-03-04 03:09:52
Share to
I get a SQL from a developer today, he was confused by its execution plan output.
SQL代码
- HELLODBA.COM> explain plan for
- 2 SELECT MAX(CNTR_MVMT_ID)
- 3 FROM CS2_CT_MVMT MVMT
- 4 WHERE MVMT.CNTR_NUM = :P_CNTR_NUM
- 5 AND MVMT.SP_COMPANY_ID = :P_SVC_PRD_ID
- 6 AND NVL(MVMT.EVENT_CURRENT_CONTRA_IND,'0')='DELETED'
- 7 AND NVL(MVMT.EVENT_CURRENT_CONTRA_IND,'0')= 'ROLLBACK'
- 8 AND exists
- 9 (select 1 from standard_event_label s where s.event_code= mvmt.event_cs_cde
- 10 and nvl(s.dimension_type, '0')= nvl(mvmt.event_cs_dimension_tp,'0')
- 11 and nvl(mvmt.event_cs_dimension_vl,'0')=nvl(s.dimension_value,'0')
- 12 and mvmt.event_cs_est_actual_ind= s.est_actual_indicator
- 13 and s.is_display='1')
- 14 and exists(select 1 from cs2_ct_bl_info ctbl where ctbl.cntr_mvmt_id=mvmt.cntr_mvmt_id
- 15 and ctbl.bl_num='NGBLAX021099');
- Explained.
- SQL> select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------------------------------------
- Plan hash value: 2642346965
- --------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 156 | 0 (0)| |
- | 1 | SORT AGGREGATE | | 1 | 156 | | |
- |* 2 | FILTER | | | | | |
- |* 3 | HASH JOIN SEMI | | 1 | 156 | 100 (2)| 00:00:02 |
- |* 4 | TABLE ACCESS BY INDEX ROWID | CS2_CT_MVMT | 1 | 109 | 2 (0)| 00:00:01 |
- | 5 | NESTED LOOPS | | 35 | 4445 | 97 (2)| 00:00:02 |
- | 6 | SORT UNIQUE | | 63 | 1134 | 63 (0)| 00:00:01 |
- | 7 | TABLE ACCESS BY INDEX ROWID| CS2_CT_BL_INFO | 63 | 1134 | 63 (0)| 00:00:01 |
- |* 8 | INDEX RANGE SCAN | CS2_CT_BL_INFO_IDX2 | 63 | | 4 (0)| 00:00:01 |
- |* 9 | INDEX RANGE SCAN | CS2_CT_MVMT_PK1 | 1 | | 1 (0)| 00:00:01 |
- |* 10 | TABLE ACCESS BY INDEX ROWID | STANDARD_EVENT_LABEL | 48 | 1392 | 2 (0)| 00:00:01 |
- |* 11 | INDEX FULL SCAN | STANDARD_EVENT_LABEL_UK1 | 55 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter(NULL IS NOT NULL)
- 3 - access("S"."EVENT_CODE"="MVMT"."EVENT_CS_CDE" AND
- NVL("S"."DIMENSION_TYPE",'0')=NVL("MVMT"."EVENT_CS_DIMENSION_TP",'0') AND
- NVL("MVMT"."EVENT_CS_DIMENSION_VL",'0')=NVL("S"."DIMENSION_VALUE",'0') AND
- "MVMT"."EVENT_CS_EST_ACTUAL_IND"="S"."EST_ACTUAL_INDICATOR")
- 4 - filter("MVMT"."CNTR_NUM"=:P_CNTR_NUM AND "MVMT"."SP_COMPANY_ID"=:P_SVC_PRD_ID AND
- ("MVMT"."EVENT_CS_EST_ACTUAL_IND"='A' OR "MVMT"."EVENT_CS_EST_ACTUAL_IND"='E' OR
- "MVMT"."EVENT_CS_EST_ACTUAL_IND"='N') AND NVL("MVMT"."EVENT_CURRENT_CONTRA_IND",'0')='DELETED')
- 8 - access("CTBL"."BL_NUM"='NGBLAX021099')
- 9 - access("CTBL"."CNTR_MVMT_ID"="MVMT"."CNTR_MVMT_ID")
- 10 - filter("S"."IS_DISPLAY"=1)
- 11 - filter("S"."EST_ACTUAL_INDICATOR" IS NOT NULL)
He was confused by the cost of the plan. Eventhough the cost of the sub plan tree is 100, the final cost is 0.
Look into the output, the 2nd filter in the predicate information, NULL IS NOT NULL. It's added by the optimizer automaticly. We know NULL IS NOT NULL is means always FALSE. Optimzier will add such filter when it found the logic result of the sub plan tree is FALSE, it will cause to the plan tree will never be executed. So, no matter how much cost of the plan tree, it will become 0 with this filter.
When find such filter added by optimizer, it's normally means a logic problem in the SQL. Review the SQL, we can find the logic defect quickly:
SQL代码
- 6 AND NVL(MVMT.EVENT_CURRENT_CONTRA_IND,'0')='DELETED'
- 7 AND NVL(MVMT.EVENT_CURRENT_CONTRA_IND,'0')= 'ROLLBACK'
This means 'DELETED'='ROLLBACK', impossible. Double check with the developer, it's his typo. The "=" should be "!=".
--- Fuyuncat ---