Code:
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE TABLE shipment_line (
2 sales_organization_cd NUMBER,
3 reference_document_no NUMBER,
4 reference_document_item_no NUMBER,
5 source_nm VARCHAR2 (10),
6 shipment_no NUMBER,
7 shipment_item_no NUMBER,
8 sales_document_type VARCHAR2 (2),
9 reportable_flag VARCHAR2 (1),
10 sab101_flag VARCHAR2 (1),
11 country_cd VARCHAR2 (1),
12 ship_to_customer VARCHAR2 (1),
13 so_entry_date DATE,
14 sales_order_item_short_tx VARCHAR2 (2));
Table created.
SQL> CREATE TABLE temp_slordt (
2 sales_organization_cd NUMBER,
3 sales_order_no NUMBER,
4 sales_order_item_no NUMBER,
5 source_nm VARCHAR2 (10));
Table created.
SQL> CREATE TABLE temp_slsord (
2 sales_organization_cd NUMBER,
3 sales_order_no NUMBER,
4 sales_order_item_no NUMBER,
5 source_nm VARCHAR2 (10));
Table created.
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> MERGE INTO shipment_line x
2 USING (SELECT *
3 FROM shipment_line sd
4 WHERE (sd.sales_organization_cd,
5 sd.reference_document_no,
6 sd.reference_document_item_no,
7 SUBSTR (sd.source_nm, -3, 3)) NOT IN (
8 SELECT /*+ HASH_AJ */
9 s.sales_organization_cd, s.sales_order_no,
10 s.sales_order_item_no, SUBSTR (s.source_nm, -3, 3)
11 FROM temp_slordt s)
12 AND sales_document_type <> '??'
13 AND EXISTS (
14 SELECT NULL
15 FROM temp_slsord s
16 WHERE s.sales_organization_cd = sd.sales_organization_cd
17 AND s.sales_order_no = sd.reference_document_no
18 AND SUBSTR (s.source_nm, -3, 3) = SUBSTR (sd.source_nm, -3, 3))) y
19 ON (x.sales_organization_cd = y.sales_organization_cd
20 AND x.shipment_no = y.shipment_no
21 AND x.shipment_item_no = y.shipment_item_no
22 AND x.source_nm = y.source_nm)
23 WHEN MATCHED THEN UPDATE
24 SET x.sales_document_type = '??', x.reportable_flag = '?',
25 x.sab101_flag = '?', x.country_cd = '?',
26 x.ship_to_customer = '?', x.so_entry_date = '31-Dec-2999',
27 x.sales_order_item_short_tx = '??'
28 WHEN NOT MATCHED THEN INSERT (
29 x.sales_organization_cd, x.shipment_no,
30 x.shipment_item_no, x.source_nm)
31 VALUES (y.sales_organization_cd, y.shipment_no,
32 y.shipment_item_no, y.source_nm);
0 rows merged.
Execution Plan
----------------------------------------------------------
0 MERGE STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=133)
1 0 MERGE OF 'SHIPMENT_LINE'
2 1 VIEW
3 2 FILTER
4 3 HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=230)
5 4 HASH JOIN (OUTER) (Cost=5 Card=1 Bytes=197)
6 5 TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=95)
7 5 TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=102)
8 4 TABLE ACCESS (FULL) OF 'TEMP_SLSORD' (TABLE) (Cost=2 Card=1 Bytes=33)
9 3 TABLE ACCESS (FULL) OF 'TEMP_SLORDT' (TABLE) (Cost=2 Card=1 Bytes=46)
SQL> MERGE INTO shipment_line x
2 USING (SELECT *
3 FROM shipment_line sd
4 WHERE sd.sales_organization_cd IS NOT NULL
5 AND sd.reference_document_no IS NOT NULL
6 AND sd.reference_document_item_no IS NOT NULL
7 AND sd.source_nm IS NOT NULL
8 AND (sd.sales_organization_cd,
9 sd.reference_document_no,
10 sd.reference_document_item_no,
11 SUBSTR (sd.source_nm, -3, 3)) NOT IN (
12 SELECT /*+ HASH_AJ */
13 s.sales_organization_cd, s.sales_order_no,
14 s.sales_order_item_no, SUBSTR (s.source_nm, -3, 3)
15 FROM temp_slordt s
16 WHERE s.sales_organization_cd IS NOT NULL
17 AND s.sales_order_no IS NOT NULL
18 AND s.sales_order_item_no IS NOT NULL
19 AND s.source_nm IS NOT NULL)
20 AND sales_document_type <> '??'
21 AND EXISTS (
22 SELECT NULL
23 FROM temp_slsord s
24 WHERE s.sales_organization_cd = sd.sales_organization_cd
25 AND s.sales_order_no = sd.reference_document_no
26 AND SUBSTR (s.source_nm, -3, 3) = SUBSTR (sd.source_nm, -3, 3))) y
27 ON (x.sales_organization_cd = y.sales_organization_cd
28 AND x.shipment_no = y.shipment_no
29 AND x.shipment_item_no = y.shipment_item_no
30 AND x.source_nm = y.source_nm)
31 WHEN MATCHED THEN UPDATE
32 SET x.sales_document_type = '??', x.reportable_flag = '?',
33 x.sab101_flag = '?', x.country_cd = '?',
34 x.ship_to_customer = '?', x.so_entry_date = '31-Dec-2999',
35 x.sales_order_item_short_tx = '??'
36 WHEN NOT MATCHED THEN INSERT (
37 x.sales_organization_cd, x.shipment_no,
38 x.shipment_item_no, x.source_nm)
39 VALUES (y.sales_organization_cd, y.shipment_no,
40 y.shipment_item_no, y.source_nm);
0 rows merged.
Execution Plan
----------------------------------------------------------
0 MERGE STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=141)
1 0 MERGE OF 'SHIPMENT_LINE'
2 1 VIEW
3 2 HASH JOIN (ANTI) (Cost=10 Card=1 Bytes=276)
4 3 HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=230)
5 4 HASH JOIN (OUTER) (Cost=5 Card=1 Bytes=197)
6 5 TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=95)
7 5 TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=102)
8 4 TABLE ACCESS (FULL) OF 'TEMP_SLSORD' (TABLE) (Cost=2 Card=1 Bytes=33)
9 3 TABLE ACCESS (FULL) OF 'TEMP_SLORDT' (TABLE) (Cost=2 Card=1 Bytes=46)
SQL>
Please do me the honour of posting your plan for MERGE statement with IS NOT NULLs added as above.