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; ... View more

Okay. Thank you, Predrag. I will work that out with Maharaj. ... View more

Thank you so much, Leonhardi! You rock! I moved the hint to follow the word "UPSERT" as you recommended, and I can now see the SORT-MERGE reflected in the EXPLAIN PLAN, and the query runs successfully and quickly. Thank you for taking the time to reply and share your wisdom with us! (I would give you points, but I apparently have no reputation yet and none to give. I will try to remember to come back in and take care of that once I am somebody on this community forum. :>) ... View more

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) -- 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; ... View more