EBS_EBSDEV> ora plan 7wh1j104vstdg -adv PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3524807309 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Ord | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Blks | Pred | Proj | Q.B | Alias | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 23 | 0 | UPDATE STATEMENT | | | | 4106K(100)| | | | | | | | 22 | 1 | UPDATE | PO_LINE_LOCATIONS_ALL | | | | | | | | SEL$AC90CD92 | | | 19 | 2 | NESTED LOOPS | | 1 | 74 | 4106K (1)| 00:02:41 | 4096K| | 11 | | | | 17 | 3 | NESTED LOOPS | | 2048K| 74 | 4106K (1)| 00:02:41 | 4096K| | 3 | | | | 15 | 4 | VIEW | VW_NSO_1 | 2048K| 25M| 13 (0)| 00:00:01 | 13| | 1 | SET$FCA7A018 | VW_NSO_1@SEL$AC90CD92 | | 14 | 5 | SORT UNIQUE | | 2048K| 44M| 13 (0)| 00:00:01 | 13| | 1 | SET$FCA7A018 | | | 13 | 6 | UNION-ALL | | | | | | | | 1 | | | | 3 |* 7 | FILTER | | | | | | | F0 | 3 | SEL$A731BD80 | | | 2 |* 8 | TABLE ACCESS BY INDEX ROWID| PO_LINE_LOCATIONS_ALL | 1 | 10 | 3 (0)| 00:00:01 | 3| F1 | 3 | SEL$A731BD80 | PO_LINE_LOCATIONS_ALL@SEL$8 | | 1 |* 9 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 | 1 | | 2 (0)| 00:00:01 | 2| A1 | 2 | SEL$A731BD80 | PO_LINE_LOCATIONS_ALL@SEL$8 | | 6 |* 10 | FILTER | | | | | | | F0 | 4 | SEL$96467BBE | | | 5 |* 11 | TABLE ACCESS BY INDEX ROWID| PO_LINE_LOCATIONS_ALL | 1 | 16 | 4 (0)| 00:00:01 | 4| F1 | 4 | SEL$96467BBE | PO_LINE_LOCATIONS_ALL@SEL$11 | | 4 |* 12 | INDEX RANGE SCAN | PO_LINE_LOCATIONS_N15 | 1 | | 3 (0)| 00:00:01 | 3| A1 | 2 | SEL$96467BBE | PO_LINE_LOCATIONS_ALL@SEL$11 | | 9 |* 13 | FILTER | | | | | | | F0 | 4 | SEL$03235BA3 | | | 8 |* 14 | TABLE ACCESS BY INDEX ROWID| PO_LINE_LOCATIONS_ALL | 2 | 32 | 5 (0)| 00:00:01 | 5| F1 | 4 | SEL$03235BA3 | PO_LINE_LOCATIONS_ALL@SEL$14 | | 7 |* 15 | INDEX RANGE SCAN | PO_LINE_LOCATIONS_N2 | 8 | | 3 (0)| 00:00:01 | 3| A1 | 2 | SEL$03235BA3 | PO_LINE_LOCATIONS_ALL@SEL$14 | | 12 |* 16 | FILTER | | | | | | | F0 | 4 | SEL$45068F18 | | | 11 |* 17 | TABLE ACCESS BY INDEX ROWID| PO_LINE_LOCATIONS_ALL | 2048K| 44M| 1 (0)| 00:00:01 | 1| F1 | 4 | SEL$45068F18 | PO_LINE_LOCATIONS_ALL@SEL$17 | | 10 |* 18 | INDEX RANGE SCAN | PO_LINE_LOCATIONS_N5 | 1 | | 1 (0)| 00:00:01 | 1| A1 | 2 | SEL$45068F18 | PO_LINE_LOCATIONS_ALL@SEL$17 | | 16 |* 19 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 | 1 | | 1 (0)| 00:00:01 | 1| A1 | 2 | SEL$AC90CD92 | PO_LINE_LOCATIONS_ALL@SEL$2 | | 18 |* 20 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL | 1 | 61 | 2 (0)| 00:00:01 | 2| F4 | 9 | SEL$AC90CD92 | PO_LINE_LOCATIONS_ALL@SEL$2 | | 21 |* 21 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL | 1 | 16 | 3 (0)| 00:00:01 | 3| F1 | 4 | SEL$C8360722 | PO_LINES_ALL@SEL$5 | | 20 |* 22 | INDEX UNIQUE SCAN | PO_LINES_U1 | 1 | | 2 (0)| 00:00:01 | 2| A1 | 2 | SEL$C8360722 | PO_LINES_ALL@SEL$5 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------------------------------------------------------------------------------- 1 - SEL$AC90CD92 4 - SET$FCA7A018 / VW_NSO_1@SEL$AC90CD92 5 - SET$FCA7A018 7 - SEL$A731BD80 8 - SEL$A731BD80 / PO_LINE_LOCATIONS_ALL@SEL$8 9 - SEL$A731BD80 / PO_LINE_LOCATIONS_ALL@SEL$8 10 - SEL$96467BBE 11 - SEL$96467BBE / PO_LINE_LOCATIONS_ALL@SEL$11 12 - SEL$96467BBE / PO_LINE_LOCATIONS_ALL@SEL$11 13 - SEL$03235BA3 14 - SEL$03235BA3 / PO_LINE_LOCATIONS_ALL@SEL$14 15 - SEL$03235BA3 / PO_LINE_LOCATIONS_ALL@SEL$14 16 - SEL$45068F18 17 - SEL$45068F18 / PO_LINE_LOCATIONS_ALL@SEL$17 18 - SEL$45068F18 / PO_LINE_LOCATIONS_ALL@SEL$17 19 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL@SEL$2 20 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL@SEL$2 21 - SEL$C8360722 / PO_LINES_ALL@SEL$5 22 - SEL$C8360722 / PO_LINES_ALL@SEL$5 Outline Data -------------------------------------------------------------------------------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_optimizer_undo_cost_change' '12.1.0.2') OPT_PARAM('_fix_control' '8528517:0') ALL_ROWS OUTLINE_LEAF(@"SEL$C8360722") MERGE(@"SEL$7286615E" >"SEL$3") OUTLINE_LEAF(@"SEL$A731BD80") MERGE(@"SEL$8A3193DA" >"SEL$6") OUTLINE_LEAF(@"SEL$96467BBE") MERGE(@"SEL$285A8194" >"SEL$9") OUTLINE_LEAF(@"SEL$03235BA3") MERGE(@"SEL$1CF66C63" >"SEL$12") OUTLINE_LEAF(@"SEL$45068F18") MERGE(@"SEL$97CCBC9E" >"SEL$15") OUTLINE_LEAF(@"SET$FCA7A018") OUTLINE_LEAF(@"SEL$AC90CD92") UNNEST(@"SET$1" UNNEST_INNERJ_DISTINCT_VIEW) OUTLINE(@"SEL$3") OUTLINE(@"SEL$7286615E") MERGE(@"SEL$5" >"SEL$4") OUTLINE(@"SEL$6") OUTLINE(@"SEL$8A3193DA") MERGE(@"SEL$8" >"SEL$7") OUTLINE(@"SEL$9") OUTLINE(@"SEL$285A8194") MERGE(@"SEL$11" >"SEL$10") OUTLINE(@"SEL$12") OUTLINE(@"SEL$1CF66C63") MERGE(@"SEL$14" >"SEL$13") OUTLINE(@"SEL$15") OUTLINE(@"SEL$97CCBC9E") MERGE(@"SEL$17" >"SEL$16") OUTLINE(@"SET$1") OUTLINE(@"SEL$1DB62934") MERGE(@"SEL$F5BB74E1" >"UPD$1") OUTLINE(@"SEL$4") OUTLINE(@"SEL$5") OUTLINE(@"SEL$7") OUTLINE(@"SEL$8") OUTLINE(@"SEL$10") OUTLINE(@"SEL$11") OUTLINE(@"SEL$13") OUTLINE(@"SEL$14") OUTLINE(@"SEL$16") OUTLINE(@"SEL$17") OUTLINE(@"UPD$1") OUTLINE(@"SEL$F5BB74E1") MERGE(@"SEL$2" >"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") NO_ACCESS(@"SEL$AC90CD92" "VW_NSO_1"@"SEL$AC90CD92") INDEX(@"SEL$AC90CD92" "PO_LINE_LOCATIONS_ALL"@"SEL$2" ("PO_LINE_LOCATIONS_ALL"."LINE_LOCATION_ID")) LEADING(@"SEL$AC90CD92" "VW_NSO_1"@"SEL$AC90CD92" "PO_LINE_LOCATIONS_ALL"@"SEL$2") USE_NL(@"SEL$AC90CD92" "PO_LINE_LOCATIONS_ALL"@"SEL$2") NLJ_BATCHING(@"SEL$AC90CD92" "PO_LINE_LOCATIONS_ALL"@"SEL$2") INDEX_RS_ASC(@"SEL$45068F18" "PO_LINE_LOCATIONS_ALL"@"SEL$17" ("PO_LINE_LOCATIONS_ALL"."PO_RELEASE_ID")) INDEX_RS_ASC(@"SEL$03235BA3" "PO_LINE_LOCATIONS_ALL"@"SEL$14" ("PO_LINE_LOCATIONS_ALL"."PO_HEADER_ID" "PO_LINE_LOCATIONS_ALL"."PO_LINE_ID")) INDEX_RS_ASC(@"SEL$96467BBE" "PO_LINE_LOCATIONS_ALL"@"SEL$11" ("PO_LINE_LOCATIONS_ALL"."PO_LINE_ID" "PO_LINE_LOCATIONS_ALL"."CONSIGNED_FLAG" "PO_LINE_LOCATIONS_ALL"."QUANTITY" "PO_LINE_LOCATIONS_ALL"."AMOUNT" "PO_LINE_LOCATIONS_ALL"."AMOUNT_CANCELLED" "PO_LINE_LOCATIONS_ALL"."QUANTITY_CANCELLED" "PO_LINE_LOCATIONS_ALL"."PRICE_OVERRIDE")) INDEX_RS_ASC(@"SEL$A731BD80" "PO_LINE_LOCATIONS_ALL"@"SEL$8" ("PO_LINE_LOCATIONS_ALL"."LINE_LOCATION_ID")) INDEX_RS_ASC(@"SEL$C8360722" "PO_LINES_ALL"@"SEL$5" ("PO_LINES_ALL"."PO_LINE_ID")) END_OUTLINE_DATA */ Peeked Binds (identified by position): --------------------------------------------------------------------------------------------------------------- 1 - (NUMBER): 19425935 4 - (NUMBER): (null) 7 - (NUMBER): (null) 12 - (NUMBER): (null) Predicate Information (identified by operation id): ---------------------------------------------------------------------------------------------------------------------------- 7 - filter(:B2=:B1) 8 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))) 9 - access("LINE_LOCATION_ID"=:B3) 10 - filter(:B2=:B4) 11 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))) 12 - access("PO_LINE_ID"=:B3) 13 - filter((:B7<>:B6 AND :B2=:B5)) 14 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))) 15 - access("PO_HEADER_ID"=:B3) 16 - filter((:B2=:B5 AND :B7=:B6)) 17 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))) 18 - access("PO_RELEASE_ID"=:B3) 19 - access("LINE_LOCATION_ID"="LINE_LOCATION_ID") 20 - filter(("SECONDARY_UNIT_OF_MEASURE" IS NOT NULL AND NVL("CANCEL_FLAG",'N')='I' AND "ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id')) AND NVL("CLOSED_CODE",'OPEN')<>'FINALLY CLOSED')) 21 - filter("ORG_ID"=TO_NUMBER(SYS_CONTEXT('multi_org2','current_org_id'))) 22 - access("PO_LINE_ID"=:B1) Column Projection Information (identified by operation id): ------------------------------------------------------------------------------------------------------------------------------------ 2 - (upd=11; cmp=3,4,5,6,7,8,9,10,11) "LINE_LOCATION_ID"[NUMBER,22], "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_LINE_ID"[NUMBER,22], "QUANTITY_CANCELLED"[NUMBER,22], "UNIT_MEAS_LOOKUP_CODE"[VARCHAR2,25], "CANCEL_FLAG"[VARCHAR2,1], "CLOSED_CODE"[VARCHAR2,30], "ORG_ID"[NUMBER,22], "SECONDARY_UNIT_OF_MEASURE"[VARCHAR2,25], "SECONDARY_QUANTITY_CANCELLED"[NUMBER,22] 3 - "LINE_LOCATION_ID"[NUMBER,22], "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22] 4 - "LINE_LOCATION_ID"[NUMBER,22] 5 - (#keys=1) STRDEF[22] 6 - STRDEF[22] 7 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 8 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 9 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22] 10 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_LINE_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 11 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_LINE_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 12 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "PO_LINE_ID"[NUMBER,22] 13 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_HEADER_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 14 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_HEADER_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 15 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "PO_HEADER_ID"[NUMBER,22] 16 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_RELEASE_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 17 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22], "PO_RELEASE_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 18 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "PO_RELEASE_ID"[NUMBER,22] 19 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "LINE_LOCATION_ID"[NUMBER,22] 20 - "PO_LINE_LOCATIONS_ALL".ROWID[ROWID,10], "PO_LINE_ID"[NUMBER,22], "QUANTITY_CANCELLED"[NUMBER,22], "UNIT_MEAS_LOOKUP_CODE"[VARCHAR2,25], "CANCEL_FLAG"[VARCHAR2,1], "CLOSED_CODE"[VARCHAR2,30], "ORG_ID"[NUMBER,22], "SECONDARY_UNIT_OF_MEASURE"[VARCHAR2,25], "SECONDARY_QUANTITY_CANCELLED"[NUMBER,22] 21 - "PO_LINES_ALL".ROWID[ROWID,10], "PO_LINE_ID"[NUMBER,22], "ITEM_ID"[NUMBER,22], "ORG_ID"[NUMBER,22] 22 - "PO_LINES_ALL".ROWID[ROWID,10], "PO_LINE_ID"[NUMBER,22] Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 13 ---------------------------------------------------------------------------------------------------------------------------------------------------- 0 - SEL$1CF66C63 - MERGE(@"SEL$1CF66C63" >"SEL$12") 0 - SEL$285A8194 - MERGE(@"SEL$285A8194" >"SEL$9") 0 - SEL$5 - MERGE(@"SEL$5" >"SEL$4") 0 - SEL$7286615E - MERGE(@"SEL$7286615E" >"SEL$3") 0 - SEL$8 - MERGE(@"SEL$8" >"SEL$7") 0 - SEL$8A3193DA - MERGE(@"SEL$8A3193DA" >"SEL$6") 0 - SEL$97CCBC9E - MERGE(@"SEL$97CCBC9E" >"SEL$15") 1 - SEL$AC90CD92 - LEADING(@SEL$AC90CD92 VW_NSO_1@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2) 5 - SET$FCA7A018 - UNNEST(@"SET$1" UNNEST_INNERJ_DISTINCT_VIEW) 19 - SEL$AC90CD92 / PO_LINE_LOCATIONS_ALL@SEL$2 - INDEX(@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2 (PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID)) - NLJ_BATCHING(@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2) - USE_NL(@SEL$AC90CD92 PO_LINE_LOCATIONS_ALL@SEL$2) 21 - SEL$C8360722 / PO_LINES_ALL@SEL$5 - INDEX_RS_ASC(@SEL$C8360722 PO_LINES_ALL@SEL$5 (PO_LINES_ALL.PO_LINE_ID)) Note ----- - SQL patch "7wh1j104vstdg" used for this statement EBS_EBSDEV> spool off;