Here is some additional information about the issue Maharaj and I (Tom Forsyth) are sharing.
The table structures involved in the this failing query are provided below. Here are the counts of records in each table.
RPT_LAB_RSLT_PRE1 = 19,729,760
RPT_LAB_RSLT_PRE2 = 2,393,244
RPT_LAB_RSLT_PRE3 = 0 (count prior to upsert attempt)
Maharaj mentions a "limit" condition, but the query provided by him with the log results does not include that. Here is the version of the query that we tried which does run successfully with the limit condition. Ultimately, we need the query Maharaj has provided to run without issue, as we do not want to limit the number of procedure descriptions joined into the data set. The limited version of the query was just used to confirm that the query syntax is sound, and that the problem appears to be caused by trying to join two source tables having large record volumes.
UPSERT
INTO MED_DOC_DM.RPT_LAB_RSLT_PRE3
SELECT
/*+ USE_SORT_MERGE_JOIN */
rslt.ORD_PROC_ID,
rslt.ORD_DT_REAL_NBR,
rslt.ORD_RSLT_LN_NBR,
rslt.PTNT_ENCNTR_CSN_ID,
rslt.PTNT_ID,
op2.ORD_PROC_DESCR,
rslt.SRVC_AREA_ID,
rslt.CMPNT_ID,
NULL AS COMPONENT_NM,
rslt.RSLT_TSP,
rslt.ORD_VAL_MEAS_TXT,
rslt.REF_UNIT_TXT,
NULL AS CBC_IND,
NULL AS HGB_IND,
NULL AS PLT_IND,
NULL AS WBC_IND,
TO_CHAR(CURRENT_TIME())
FROM
MED_DOC_DM.RPT_LAB_RSLT_PRE1 rslt
JOIN (
SELECT op.ORD_PROC_ID, op.ORD_PROC_DESCR
FROM MED_DOC_DM.RPT_LAB_RSLT_PRE2 op
LIMIT 500 )
op2
ON rslt.ORD_PROC_ID = op2.ORD_PROC_ID;
-- This table is the receiver of the upsert record set.
CREATE
TABLE MED_DOC_DM.RPT_LAB_RSLT_PRE3
(
ORD_PROC_ID
VARCHAR,
ORD_DT_REAL_NBR
VARCHAR,
ORD_RSLT_LN_NBR
VARCHAR,
PTNT_ENCNTR_CSN_ID
VARCHAR,
PTNT_ID
VARCHAR,
ORD_PROC_DESCR
VARCHAR,
SRVC_AREA_ID
VARCHAR,
CMPNT_ID
VARCHAR,
CMPNT_NM
VARCHAR,
RSLT_TSP
VARCHAR,
ORD_VAL_MEAS_TXT
VARCHAR,
REF_UNIT_TXT
VARCHAR,
CBC_IND
VARCHAR,
HGB_IND
VARCHAR,
PLT_IND
VARCHAR,
WBC_IND
VARCHAR,
ROW_INSERT_TSP
VARCHAR
CONSTRAINT PK_RPT_LAB_RSLT_PRE3 PRIMARY KEY( ORD_PROC_ID, ORD_DT_REAL_NBR,
ORD_RSLT_LN_NBR ) )
IMMUTABLE_ROWS=true,
COMPRESSION='SNAPPY', SALT_BUCKETS = 12;
-- This is the first source table being queried, providing the bulk of the data being populated to the receiver table.
CREATE
TABLE MED_DOC_DM.RPT_LAB_RSLT_PRE1
(
ORD_PROC_ID
VARCHAR,
ORD_DT_REAL_NBR
VARCHAR,
ORD_RSLT_LN_NBR
VARCHAR,
PTNT_ENCNTR_CSN_ID
VARCHAR,
PTNT_ID
VARCHAR,
SRVC_AREA_ID
VARCHAR,
CMPNT_ID
VARCHAR,
RSLT_TSP
VARCHAR,
ORD_VAL_MEAS_TXT
VARCHAR,
REF_UNIT_TXT
VARCHAR,
ROW_INSERT_TSP
VARCHAR
CONSTRAINT PK_RPT_LAB_RSLT_PRE1 PRIMARY KEY( ORD_PROC_ID, ORD_DT_REAL_NBR,
ORD_RSLT_LN_NBR ) )
IMMUTABLE_ROWS=true,
COMPRESSION='SNAPPY', SALT_BUCKETS = 12;
-- This table is being joined in as the 2nd of the source tables to effectively attach the procedure description to each
-- record from the 1st source table.
CREATE TABLE MED_DOC_DM.RPT_LAB_RSLT_PRE2
(
ORD_PROC_ID VARCHAR,
ORD_PROC_DESCR VARCHAR,
ROW_INSERT_TSP VARCHAR
CONSTRAINT PK_RPT_LAB_RSLT_PRE2 PRIMARY KEY( ORD_PROC_ID ) )
IMMUTABLE_ROWS=true, COMPRESSION='SNAPPY', SALT_BUCKETS = 12;
