SELECT .... FROM (SELECT .... FROM (SELECT .... FROM "USER1"."COX" COX -- LEFT JOIN "USER1"."BOS" "BOS" ON ( "BOS"."BID" = "COX"."BID" AND "BOS"."OID" = TO_NUMBER ("COX"."OID")) LEFT JOIN "USER1"."CS" CS ON "COX"."ECID" = "CS"."ECID" ) ) "A1" WHERE "COX"."BID" = :b1 UNION ALL SELECT....... FROM (SELECT ..... FROM (SELECT ..... FROM "USER1"."COX" COX -- LEFT JOIN "USER1"."BOS" "BOS" ON ( "BOS"."BID" = "COX"."BID" AND "BOS"."OID" = TO_NUMBER ("COX"."OID")) LEFT JOIN "USER1"."CS" CS ON "COX"."ECID" = "CS"."ECID" ) ) "A1" WHERE "BOS"."BID" = :b2 and lnnvl("COX"."BID" = :b1) --BOS AND "A1"."I_DT" IS NULL --BOS AND ( "COX"."BID" IS NOT NULL AND "COX"."CT3" = 'XXX' --COX OR "COX"."BID" IS NULL AND "COX"."CT3" = 'YYY') Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16777216 Execution Started : 12/30/2020 10:57:24 First Refresh Time : 12/30/2020 10:57:24 Last Refresh Time : 12/30/2020 10:57:25 Duration : 1s Program : Toad.exe Fetch Calls : 1 Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.30 | 0.30 | 0.00 | 1 | 56955 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=542343301) ===================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ===================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 3 | | | | 1 | UNION-ALL | | | | 1 | +1 | 1 | 3 | | | | 2 | NESTED LOOPS OUTER | | 2 | 3216 | 1 | +1 | 1 | 3 | | | | 3 | NESTED LOOPS OUTER | | 2 | 3212 | 1 | +1 | 1 | 3 | | | | 4 | TABLE ACCESS STORAGE FULL | COX | 2 | 3209 | 1 | +1 | 1 | 3 | 100.00 | Cpu (1) | | 5 | TABLE ACCESS BY INDEX ROWID | CS | 1 | 2 | 1 | +1 | 3 | 1 | | | | 6 | INDEX UNIQUE SCAN | CS_PK | 1 | 1 | 1 | +1 | 3 | 1 | | | | 7 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 2 | 1 | +1 | 3 | 2 | | | | 8 | INDEX UNIQUE SCAN | BOS_PK | 1 | 1 | 1 | +1 | 3 | 2 | | | | 9 | NESTED LOOPS | | 1 | 3211 | | | 1 | | | | | 10 | NESTED LOOPS | | 1 | 3211 | | | 1 | | | | | 11 | NESTED LOOPS OUTER | | 1 | 3209 | | | 1 | | | | | 12 | TABLE ACCESS STORAGE FULL | COX | 1 | 3207 | | | 1 | | | | | 13 | TABLE ACCESS BY INDEX ROWID | CS | 1 | 2 | | | | | | | | 14 | INDEX UNIQUE SCAN | CS_PK | 1 | 1 | | | | | | | | 15 | INDEX UNIQUE SCAN | BOS_PK | 1 | 1 | | | | | | | | 16 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 2 | | | | | | | ===================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 4 - storage("COX"."BID"=TO_NUMBER(:B1)) filter("COX"."BID"=TO_NUMBER(:B1)) 6 - access("COX"."EID"="CS"."EID"(+)) 8 - access("BOS"."BID"(+)=TO_NUMBER(:B1) AND "BOS"."OID"(+)=TO_NUMBER("COX"."OID")) 12 - storage("COX"."CT3"='XXX' AND "COX"."BID"=TO_NUMBER(:B2) AND LNNVL("COX"."BID"=TO_NUMBER(:B1))) filter("COX"."CT3"='XXX' AND "COX"."BID"=TO_NUMBER(:B2) AND LNNVL("COX"."BID"=TO_NUMBER(:B1))) 14 - access("COX"."EID"="CS"."EID"(+)) 15 - access("BOS"."BID"=TO_NUMBER(:B2) AND "BOS"."OID"=TO_NUMBER("COX"."OID")) 16 - filter("BOS"."I_DT" IS NULL)