Explain Plan for Query not using "IS NULL" condition... --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Inst |IN-OUT| --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1549 | 3352K| 461 (2)| | | |* 1 | HASH JOIN | | 1549 | 3352K| 461 (2)| | | | 2 | NESTED LOOPS | | 19 | 4066 | 435 (2)| | | | 3 | NESTED LOOPS | | 19 | 3762 | 430 (2)| | | |* 4 | HASH JOIN | | 379 | 47375 | 240 (3)| | | |* 5 | HASH JOIN | | 135 | 2970 | 34 (6)| | | |* 6 | TABLE ACCESS FULL | CPAC_PROVISIONING_EXCLUDE_EXT | 135 | 945 | 2 (0)| | | | 7 | VIEW | index$_join$_006 | 15680 | 229K| 31 (4)| | | |* 8 | HASH JOIN | | | | | | | | 9 | INDEX FAST FULL SCAN | D_STUDY_PK | 15680 | 229K| 9 (0)| | | | 10 | INDEX FAST FULL SCAN | D_STUDY_UK01 | 15680 | 229K| 21 (0)| | | | 11 | REMOTE | | 44217 | 4447K| 205 (2)| CTMSP~ | R->S | | 12 | REMOTE | TRIAL | 1 | 13 | 1 (0)| CTMSP~ | R->S | | 13 | REMOTE | PERSONNEL | 1 | 16 | 1 (0)| CTMSP~ | R->S | | 14 | VIEW | VW_NSO_1 | 8168 | 15M| 26 (8)| | | | 15 | SORT UNIQUE | | 8168 | 16336 | 26 (8)| | | | 16 | COLLECTION ITERATOR PICKLER FETCH| FN_GET_PARAMETER_MULTI_API | | | | | | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TUP"."OCCUPATION_CODE"="O_PARM_VALUE_TXT") 4 - access("D"."PROTOCOL_NM"=SUBSTR("TU"."TRIAL_ALIAS_CODE",1,INSTR("TU"."TRIAL_ALIAS_CODE",'-',1,2)-1)) 5 - access("C"."STUDY_KEY"="D"."STUDY_KEY") 6 - filter("C"."CPAC_EXCLUSION"='E' OR "C"."CPAC_EXCLUSION"='X') 8 - access(ROWID=ROWID) Remote SQL Information (identified by operation id): ---------------------------------------------------- 11 - SELECT "A1"."CANCEL_STOP_FLAG","A1"."TRIAL_UNIT_STATUS","A1"."CANCEL_STOP_FLAG","A1"."TRIAL_UNIT_STATUS","A1 "."HOLD_FLAG","A1"."CONFIRMED_FLAG","A1"."TRIAL_UNIT_REFERENCE","A1"."TRIAL_UNIT_REFERENCE","A1"."TRIAL_ALIAS_CODE" ,"A1"."TRIAL_ALIAS_CODE","A1"."TRIAL_NO","A1"."TRIAL_NO","A1"."COUNTRY_CODE","A1"."UNIT_NO","A2"."TRIAL_NO","A2"."C OUNTRY_CODE","A2"."UNIT_NO","A2"."PERSONNEL_NO","A2"."OCCUPATION_CODE","A2"."TRIAL_NO","A2"."COUNTRY_CODE","A2"."UN IT_NO","A2"."PERSONNEL_NO","A2"."OCCUPATION_CODE","A2"."END_DATE" FROM "TRIAL_UNIT" "A1","TU_PERSONNEL" "A2" WHERE "A2"."END_DATE">=SYSDATE@! AND "A1"."TRIAL_NO"="A2"."TRIAL_NO" AND "A1"."COUNTRY_CODE"="A2"."COUNTRY_CODE" AND "A1"."UNIT_NO"="A2"."UNIT_NO" (accessing 'CTMSPRS1.MERCK' ) 12 - SELECT "TRIAL_NO" FROM "TRIAL" "T" WHERE "TRIAL_NO"=:1 (accessing 'CTMSPRS1.MERCK' ) 13 - SELECT "PERSONNEL_NO","DISCONTINUED_FLAG" FROM "PERSONNEL" "P" WHERE "PERSONNEL_NO"=:1 (accessing 'CTMSPRS1.MERCK' ) Note ----- - 'PLAN_TABLE' is old version ------------------------------------------------------------------------------------------------------- Explain Plan for Query Using "IS NULL" condition :- --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Inst |IN-OUT| --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3021 | 6537K| 466 (2)| | | |* 1 | HASH JOIN | | 3021 | 6537K| 466 (2)| | | | 2 | NESTED LOOPS | | 37 | 7918 | 440 (2)| | | | 3 | NESTED LOOPS | | 37 | 7326 | 430 (2)| | | |* 4 | HASH JOIN | | 379 | 47375 | 240 (3)| | | |* 5 | HASH JOIN | | 135 | 2970 | 34 (6)| | | |* 6 | TABLE ACCESS FULL | CPAC_PROVISIONING_EXCLUDE_EXT | 135 | 945 | 2 (0)| | | | 7 | VIEW | index$_join$_006 | 15680 | 229K| 31 (4)| | | |* 8 | HASH JOIN | | | | | | | | 9 | INDEX FAST FULL SCAN | D_STUDY_PK | 15680 | 229K| 9 (0)| | | | 10 | INDEX FAST FULL SCAN | D_STUDY_UK01 | 15680 | 229K| 21 (0)| | | | 11 | REMOTE | | 44217 | 4447K| 205 (2)| CTMSP~ | R->S | | 12 | REMOTE | TRIAL | 1 | 13 | 1 (0)| CTMSP~ | R->S | | 13 | REMOTE | PERSONNEL | 1 | 16 | 1 (0)| CTMSP~ | R->S | | 14 | VIEW | VW_NSO_1 | 8168 | 15M| 26 (8)| | | | 15 | SORT UNIQUE | | 8168 | 16336 | 26 (8)| | | | 16 | COLLECTION ITERATOR PICKLER FETCH| FN_GET_PARAMETER_MULTI_API | | | | | | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TUP"."OCCUPATION_CODE"="O_PARM_VALUE_TXT") 4 - access("D"."PROTOCOL_NM"=SUBSTR("TU"."TRIAL_ALIAS_CODE",1,INSTR("TU"."TRIAL_ALIAS_CODE",'-',1,2)-1)) 5 - access("C"."STUDY_KEY"="D"."STUDY_KEY") 6 - filter("C"."CPAC_EXCLUSION"='E' OR "C"."CPAC_EXCLUSION"='X') 8 - access(ROWID=ROWID) Remote SQL Information (identified by operation id): ---------------------------------------------------- 11 - SELECT "A1"."CANCEL_STOP_FLAG","A1"."TRIAL_UNIT_STATUS","A1"."CANCEL_STOP_FLAG","A1"."TRIAL_UNIT_STATUS","A1 "."HOLD_FLAG","A1"."CONFIRMED_FLAG","A1"."TRIAL_UNIT_REFERENCE","A1"."TRIAL_UNIT_REFERENCE","A1"."TRIAL_ALIAS_CODE" ,"A1"."TRIAL_ALIAS_CODE","A1"."TRIAL_NO","A1"."TRIAL_NO","A1"."COUNTRY_CODE","A1"."UNIT_NO","A2"."TRIAL_NO","A2"."C OUNTRY_CODE","A2"."UNIT_NO","A2"."PERSONNEL_NO","A2"."OCCUPATION_CODE","A2"."TRIAL_NO","A2"."COUNTRY_CODE","A2"."UN IT_NO","A2"."PERSONNEL_NO","A2"."OCCUPATION_CODE","A2"."END_DATE","A2"."END_DATE" FROM "TRIAL_UNIT" "A1","TU_PERSONNEL" "A2" WHERE ("A2"."END_DATE" IS NULL OR "A2"."END_DATE">=SYSDATE@!) AND "A1"."TRIAL_NO"="A2"."TRIAL_NO" AND "A1"."COUNTRY_CODE"="A2"."COUNTRY_CODE" AND "A1"."UNIT_NO"="A2"."UNIT_NO" (accessing 'CTMSPRS1.MERCK' ) 12 - SELECT "TRIAL_NO" FROM "TRIAL" "T" WHERE "TRIAL_NO"=:1 (accessing 'CTMSPRS1.MERCK' ) 13 - SELECT "PERSONNEL_NO","DISCONTINUED_FLAG" FROM "PERSONNEL" "P" WHERE "PERSONNEL_NO"=:1 (accessing 'CTMSPRS1.MERCK' ) Note ----- - 'PLAN_TABLE' is old version