*********** First part of UNION query*************** SELECT ....~58 columns projected... FROM (SELECT ....~60 columns projected FROM "USER1"."BOS" "A2" FULL OUTER JOIN (SELECT ...~41 columns projected from A4 and A5 FROM "USER1"."CS" "A4" FULL OUTER JOIN "USER1"."COX" "A5" ON "A5"."EID" = "A4"."EID") "A3" ON "A2"."BI" = "A3"."BID1" AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1" WHERE "A1"."BI0" = :b1 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777216 Execution Started : 12/27/2020 12:37:03 First Refresh Time : 12/27/2020 12:37:03 Last Refresh Time : 12/27/2020 12:37:24 Duration : 21s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 2 Global Stats =========================================================================================== | Elapsed | Cpu | IO | Fetch | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload | =========================================================================================== | 23 | 7.12 | 16 | 2 | 52223 | 33366 | 4GB | 2843 | 333MB | -16.28% | =========================================================================================== SQL Plan Monitoring Details (Plan Hash Value=1688253567) ================================================================================================================================================================================================================ | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ================================================================================================================================================================================================================ | 0 | SELECT STATEMENT | | | | 15 | +3 | 1 | 3 | | | | | | | | | | 1 | VIEW | VW_FOJ_0 | 2M | 16685 | 15 | +3 | 1 | 3 | | | | | | | | | | 2 | HASH JOIN RIGHT OUTER | | 2M | 16685 | 16 | +2 | 1 | 3 | | | | | 447K | | | | | 3 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 4 | 1 | +2 | 1 | 3 | | | | | | | | | | 4 | INDEX RANGE SCAN | BOS_PK | 1 | 3 | 1 | +2 | 1 | 3 | | | | | | | | | | 5 | VIEW | | 2M | 16678 | 15 | +3 | 1 | 3 | | | | | | | | | | 6 | VIEW | VW_FOJ_1 | 2M | 16678 | 20 | +2 | 1 | 3 | | | | | | | | | | 7 | HASH JOIN RIGHT OUTER | | 2M | 16678 | 22 | +0 | 1 | 2M | 33366 | 4GB | 2843 | 333MB | 2M | 373M | 100.00 | Cpu (3) | | | | | | | | | | | | | | | | | | direct path read temp (18) | | 8 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 | 1 | +2 | 1 | 948K | | | | | | | | | | 9 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 1 | +2 | 1 | 2M | | | | | | | | | ================================================================================================================================================================================================================ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A2"."BI"(+)="A3"."BID1" AND "A2"."OID"(+)=TO_NUMBER("A3"."OID2")) 4 - access("A2"."BI"(+)=TO_NUMBER(:B1)) 6 - filter("A5"."BI"=TO_NUMBER(:B1)) 7 - access("A5"."EID"="A4"."EID"(+)) **************** Second part of UNION query******************* SELECT ....~58 columns projected... FROM (SELECT ....~60 columns projected FROM "USER1"."BOS" "A2" FULL OUTER JOIN (SELECT ...~41 columns projected from A4 and A5 FROM "USER1"."CS" "A4" FULL OUTER JOIN "USER1"."COX" "A5" ON "A5"."EID" = "A4"."EID") "A3" ON "A2"."BI" = "A3"."BID1" AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1" WHERE "A1"."COl1" = :b2 AND "A1"."I_DT" IS NULL AND ( "A1"."BI0" IS NOT NULL AND "A1"."CT1" = 'XXX' OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY') Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777216 Execution Started : 12/27/2020 12:38:56 First Refresh Time : 12/27/2020 12:38:56 Last Refresh Time : 12/27/2020 12:39:37 Duration : 41s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 2 Global Stats ========================================================================================================= | Elapsed | Cpu | IO | Concurrency | Fetch | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload | ========================================================================================================= | 45 | 13 | 32 | 0.00 | 2 | 52223 | 66713 | 8GB | 2843 | 333MB | -8.70% | ========================================================================================================= SQL Plan Monitoring Details (Plan Hash Value=926058519) =============================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | =============================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 36 | +6 | 1 | 2 | | | | | | | | | | 1 | FILTER | | | | 36 | +6 | 1 | 2 | | | | | | | | | | 2 | HASH JOIN OUTER | | 1 | 16685 | 40 | +2 | 1 | 3 | | | | | 429K | | | | | 3 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 4 | 1 | +2 | 1 | 3 | | | | | | | | | | 4 | INDEX RANGE SCAN | BOS_PK | 1 | 3 | 1 | +2 | 1 | 3 | | | | | | | | | | 5 | VIEW | VW_FOJ_1 | 2M | 16678 | 40 | +2 | 1 | 3 | | | | | | | | | | 6 | HASH JOIN FULL OUTER | | 2M | 16678 | 41 | +1 | 1 | 2M | 66713 | 8GB | 2843 | 333MB | 2M | 373M | 100.00 | Cpu (11) | | | | | | | | | | | | | | | | | | direct path read temp (29) | | | | | | | | | | | | | | | | | | direct path write temp (1) | | 7 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 | 1 | +2 | 1 | 948K | | | | | | | | | | 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 1 | +2 | 1 | 2M | | | | | | | | | =============================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A5"."CT1"='XXX' AND "A5"."BI" IS NOT NULL OR "A5"."BI" IS NULL AND "A5"."CT1"='YYY') 2 - access("A2"."BI"="A5"."BI"(+) AND "A2"."OID"=TO_NUMBER("A5"."OID"(+))) 3 - filter("A2"."I_DT" IS NULL) 4 - access("A2"."BI"=TO_NUMBER(:B2)) 5 - filter("A5"."BI"(+)=TO_NUMBER(:B2)) 6 - access("A5"."EID"="A4"."EID") *********** With UNION ****************** SELECT ....~58 columns projected... FROM (SELECT ....~60 columns projected FROM "USER1"."BOS" "A2" FULL OUTER JOIN (SELECT ...~41 columns projected from A4 and A5 FROM "USER1"."CS" "A4" FULL OUTER JOIN "USER1"."COX" "A5" ON "A5"."EID" = "A4"."EID") "A3" ON "A2"."BI" = "A3"."BID1" AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1" WHERE "A1"."BI0" = :b1 UNION SELECT ....~58 columns projected... FROM (SELECT ....~60 columns projected FROM "USER1"."BOS" "A2" FULL OUTER JOIN (SELECT ...~41 columns projected from A4 and A5 FROM "USER1"."CS" "A4" FULL OUTER JOIN "USER1"."COX" "A5" ON "A5"."EID" = "A4"."EID") "A3" ON "A2"."BI" = "A3"."BID1" AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1" WHERE "A1"."COl1" = :b2 AND "A1"."I_DT" IS NULL AND ( "A1"."BI0" IS NOT NULL AND "A1"."CT1" = 'XXX' OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY') Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777216 Execution Started : 12/27/2020 12:21:08 First Refresh Time : 12/27/2020 12:21:12 Last Refresh Time : 12/27/2020 12:21:50 Duration : 42s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 2 Global Stats =========================================================================================== | Elapsed | Cpu | IO | Fetch | Buffer | Read | Read | Write | Write | Cell | | Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload | =========================================================================================== | 46 | 13 | 33 | 2 | 80710 | 66713 | 8GB | 2843 | 333MB | -8.70% | =========================================================================================== SQL Plan Monitoring Details (Plan Hash Value=3655576753) ================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 39 | +4 | 1 | 3 | | | | | | | | 1 | SORT UNIQUE | | 3 | 19901 | 39 | +4 | 1 | 3 | | | 2048 | | | | | 2 | UNION-ALL | | | | 31 | +4 | 1 | 5 | | | | | | | | 3 | NESTED LOOPS OUTER | | 2 | 3214 | 1 | +4 | 1 | 3 | | | | | | | | 4 | VIEW | VW_FOJ_1 | 2 | 3210 | 1 | +4 | 1 | 3 | | | | | | | | 5 | NESTED LOOPS OUTER | | 2 | 3210 | 1 | +4 | 1 | 3 | | | | | | | | 6 | TABLE ACCESS STORAGE FULL | COX | 2 | 3206 | 1 | +4 | 1 | 3 | | | | | | | | 7 | TABLE ACCESS BY INDEX ROWID | CS | 1 | 2 | 1 | +4 | 3 | 1 | | | | | | | | 8 | INDEX UNIQUE SCAN | CS_PK | 1 | 1 | 1 | +4 | 3 | 1 | | | | | | | | 9 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 2 | 1 | +4 | 3 | 2 | | | | | | | | 10 | INDEX UNIQUE SCAN | BOS_PK | 1 | 1 | 1 | +4 | 3 | 2 | | | | | | | | 11 | FILTER | | | | 35 | +8 | 1 | 2 | | | | | | | | 12 | HASH JOIN OUTER | | 1 | 16685 | 39 | +4 | 1 | 3 | | | 434K | | | | | 13 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 4 | 1 | +4 | 1 | 3 | | | | | | | | 14 | INDEX RANGE SCAN | BOS_PK | 1 | 3 | 1 | +4 | 1 | 3 | | | | | | | | 15 | VIEW | VW_FOJ_3 | 2M | 16678 | 39 | +4 | 1 | 3 | | | | | | | | 16 | HASH JOIN FULL OUTER | | 2M | 16678 | 42 | +1 | 1 | 2M | 63108 | 7GB | 2M | 373M | 100.00 | Cpu (11) | | | | | | | | | | | | | | | | direct path read temp (31) | | 17 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 | 1 | +4 | 1 | 948K | | | | | | | | 18 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 | 1 | +4 | 1 | 2M | | | | | | | ================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 6 - storage("A5"."BI"=TO_NUMBER(:B1)) filter("A5"."BI"=TO_NUMBER(:B1)) 8 - access("A5"."EID"="A4"."EID"(+)) 10 - access("A2"."BI"(+)=TO_NUMBER(:B1) AND "A2"."OID"(+)=TO_NUMBER("A5"."OID")) 11 - filter("A5"."CT1"='XXX' AND "A5"."BI" IS NOT NULL OR "A5"."BI" IS NULL AND "A5"."CT1"='YYY') 12 - access("A2"."BI"="A5"."BI"(+) AND "A2"."OID"=TO_NUMBER("A5"."OID"(+))) 13 - filter("A2"."I_DT" IS NULL) 14 - access("A2"."BI"=TO_NUMBER(:B2)) 15 - filter("A5"."BI"(+)=TO_NUMBER(:B2)) 16 - access("A5"."EID"="A4"."EID")