Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following DML in my Compose-generated instructions:
INSERT /*+ APPEND */
INTO "ATTUNITY_SFS"."TSTG_EXPENSE_DST_P" ("ID",
"GL_BU_CODE",
"BUD_LVL_DEPT_CODE",
"TXN_LVL_DEPT_CODE",
"FUND_CODE",
"BUD_LVL_PROGRAM_CODE",
"TXN_LVL_PROGRAM_CODE",
"BUD_LVL_ACCT_CODE",
"TXN_LVL_ACCT_CODE",
"SFS_BUDGET_REF",
"FD",
"GL_BU_DESC",
"BUD_LVL_DEPT_DESC",
"TXN_LVL_DEPT_DESC",
"FUND_DESC",
"BUD_LVL_PROGRAM_DESC",
"TXN_LVL_PROGRAM_DESC",
"BUD_LVL_ACCT_DESC",
"TXN_LVL_ACCT_DESC",
"OWB_DIM_KEY",
"CurRow",
"PrvRow",
"ObjOrd",
"ObjCnt",
"DtsOrd",
"DtsCnt",
"TopRow")
SELECT "P"."ID",
"C"."GL_BU_CODE",
"C"."BUD_LVL_DEPT_CODE",
"C"."TXN_LVL_DEPT_CODE",
"C"."FUND_CODE",
"C"."BUD_LVL_PROGRAM_CODE",
"C"."TXN_LVL_PROGRAM_CODE",
"C"."BUD_LVL_ACCT_CODE",
"C"."TXN_LVL_ACCT_CODE",
"C"."SFS_BUDGET_REF",
"C"."FD",
"C"."GL_BU_DESC",
"C"."BUD_LVL_DEPT_DESC",
"C"."TXN_LVL_DEPT_DESC",
"C"."FUND_DESC",
"C"."BUD_LVL_PROGRAM_DESC",
"C"."TXN_LVL_PROGRAM_DESC",
"C"."BUD_LVL_ACCT_DESC",
"C"."TXN_LVL_ACCT_DESC",
COALESCE ("C"."OWB_DIM_KEY", "P"."OWB_DIM_KEY"),
"C"."ROWNR",
"R"."PrvRow",
"R"."ObjOrd",
"R"."ObjCnt",
"R"."DtsOrd",
"R"."DtsCnt",
"P"."TopRow"
FROM "ATTUNITY_SFS"."TTMP_3242_Ranks" "R"
INNER JOIN "ATTUNITY_SFS"."TSTG_EXPENSE_DST" "C"
ON "R"."CurRow" = "C"."ROWNR"
INNER JOIN "ATTUNITY_SFS"."TSTG_EXPENSE_DST_P" "P"
ON "R"."PrvRow" = "P"."CurRow"
WHERE "R"."ObjOrd" = 67098
As you can see by the high ObjOrd value in the where clause, this insert has been going on for hours, despite fewer than 1500 records being updated in the HUB table, which itself has fewer than one million rows.
What is the point of this logic? There has to be a far more efficient way of doing whatever it is it's doing, which looks like it is inserting virtually one record at a time.