How can i use partition keys while merging in to a table
Hi,
we have a mapping in which we use partitioned table as target and we call the mapping in loop sending partition keys as input parameters.this mapping works as merge on target based on some 4 columns, for just 20 records it take more than 7 minutes, i tried running that merge statement directly against DB it took same time, then i used the partition keys in the merge to match it finished in 1 second. my question is can i have some kind of parameter setting which will enable to use the input parameters while merging so that it can go against single partition instead of searching the whole table,
how to merge based on partition key values along with merge keys
here is my original merge statement from mapping
MERGE INTO GL_JE_LINE_DETAIL
USING (SELECT "GL_JE_LINES_STG"."JE_LINE_ID" "JE_LINE_ID",
'4' "SOURCE",
"XXGL_AP_DRILLDOWN_V"."VENDOR_NUMBER" "VENDOR_NUMBER",
"XXGL_AP_DRILLDOWN_V"."VENDOR_NAME" "VENDOR_NAME",
"XXGL_AP_DRILLDOWN_V"."INVOICE_NUMBER" "INVOICE_NUMBER",
"XXGL_AP_DRILLDOWN_V"."PO_NUMBER" "PO_NUMBER",
"XXGL_AP_DRILLDOWN_V"."DOC_REF" "DOC_REF",
"XXGL_AP_DRILLDOWN_V"."AMOUNT" "AMOUNT",
"GL_JE_LINES_STG"."CREATION_DATE" "CREATION_DATE",
"GL_JE_LINES_STG"."LAST_UPDATE_DATE" "LAST_UPDATE_DATE",
"GL_JE_LINES_STG"."PERIOD_YEAR" "PERIOD_YEAR",
"GL_JE_LINES_STG"."PERIOD_MONTH" "PERIOD_MONTH",
"GL_JE_LINES_STG"."ENTITY_CODE" "ENTITY_CODE",
"XXGL_AP_DRILLDOWN_V"."DESCRIPTION" "DESCRIPTION",
'6' "ORIG_SOURCE",
"XXGL_AP_DRILLDOWN_V"."AP_SOURCE" "AP_SOURCE",
"XXGL_AP_DRILLDOWN_V"."AE_LINE_ID" "AE_LINE_ID",
"XXGL_AP_DRILLDOWN_V"."JE_HEADER_ID" "JE_HEADER_ID_1",
"XXGL_AP_DRILLDOWN_V"."JE_LINE_NUM" "JE_LINE_NUM_1"
FROM datarepdb."GL_JE_LINES_STG" "GL_JE_LINES_STG",
datarepdb."XXGL_AP_DRILLDOWN_V" "XXGL_AP_DRILLDOWN_V"
WHERE ("GL_JE_LINES_STG"."PERIOD_YEAR" = 2009)
AND ("GL_JE_LINES_STG"."PERIOD_MONTH" = 9)
AND ("GL_JE_LINES_STG"."ENTITY_CODE" = 'GPC')
AND ("GL_JE_LINES_STG"."USER_JE_SOURCE_NAME" = 'Payables')
AND ("GL_JE_LINES_STG"."JE_HEADER_ID" = "XXGL_AP_DRILLDOWN_V"."JE_HEADER_ID")
AND ("GL_JE_LINES_STG"."JE_LINE_NUM" = "XXGL_AP_DRILLDOWN_V"."JE_LINE_NUM")) merge_subquery
ON ( "GL_JE_LINE_DETAIL"."PERIOD_YEAR" = "MERGE_SUBQUERY"."PERIOD_YEAR"
AND "GL_JE_LINE_DETAIL"."PERIOD_MONTH" = "MERGE_SUBQUERY"."PERIOD_MONTH"
AND "GL_JE_LINE_DETAIL"."ENTITY_CODE" = "MERGE_SUBQUERY"."ENTITY_CODE"
AND "GL_JE_LINE_DETAIL"."AE_LINE_ID" = "MERGE_SUBQUERY"."AE_LINE_ID")
I am merging based on the 4 columns you see at the end , i specified that on target table, this query is costly
when i change the merge part as
ON ( "GL_JE_LINE_DETAIL"."PERIOD_YEAR" = "MERGE_SUBQUERY"."PERIOD_YEAR"
AND "GL_JE_LINE_DETAIL"."PERIOD_MONTH" = "MERGE_SUBQUERY"."PERIOD_MONTH"
AND "GL_JE_LINE_DETAIL"."ENTITY_CODE" = "MERGE_SUBQUERY"."ENTITY_CODE"
AND "GL_JE_LINE_DETAIL"."AE_LINE_ID" = "MERGE_SUBQUERY"."AE_LINE_ID")
AND "GL_JE_LINE_DETAIL"."PERIOD_YEAR" = 2009
AND "GL_JE_LINE_DETAIL"."PERIOD_MONTH" = 9
AND "GL_JE_LINE_DETAIL"."ENTITY_CODE" = 'GPC')
The lines in bold in the first sql are input parameters i added the same in to merge statement then the query finished in 1 second,and i see in the explain plan that its going to single partition in other case it was doing multiple partitions
how can i add these input parameters in to the mapping.
Suresh