Performance Issue After Joining EGO_ITEM_EFF_B on the basis of CHANGE_LINE_ID. Any solutions?

Question

Hi, I’m working on a query that previously ran within seconds, but after adding the lastLEFT JOIN, the execution time has increased significantly—even for a single change order, it now takes around 2 minutes. The purpose of the join is to retrieve the redlined value for a specific attribute tied to a particular change order, which is why I’m joining on CHANGE_LINE_ID. However, this addition has impacted performance quite a bit. Could you help me understand why this join might be causing the slowdown, and suggest any optimization approaches? I need this query to run much faster for use in an automation process. Here is the code: SELECT DISTINCT
C.CHANGE_NOTICE,
ITEM.ITEM_NUMBER,
ITEM.INVENTORY_ITEM_ID,
EFF.ATTRIBUTE_CHAR2 AS "Pending Changes",
PJTNAMEH.ATTRIBUTE_CHAR8 AS "Project Name",
PJTNAME.ATTRIBUTE_CHAR8 AS "Project Name Redline" LEFT JOIN EGO_CHANGE_LINES_B CL ON CL.CHANGE_ID = C.CHANGE_ID AND CL.ORGANIZATION_ID = C.ORGANIZATION_ID
LEFT JOIN EGP_SYSTEM_ITEMS_B ITEM ON ITEM.INVENTORY_ITEM_ID = CL.PK1_VALUE AND ITEM.ORGANIZATION_ID = CL.ORGANIZATION_ID
LEFT JOIN EGO_ITEM_EFF_B EFF ON EFF.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID AND EFF.ORGANIZATION_ID = ITEM.ORGANIZATION_ID AND EFF.ACD_TYPE = 'PROD' AND EFF.CONTEXT_CODE = 'TFS_CMD_Additional_Information'
LEFT JOIN EGO_ITEM_EFF_B PJTNAMEH ON ITEM.INVENTORY_ITEM_ID = PJTNAMEH.INVENTORY_ITEM_ID AND 300000001488573 = PJTNAMEH.ORGANIZATION_ID AND PJTNAMEH.ACD_TYPE = 'PROD' AND PJTNAMEH.CONTEXT_CODE = 'TFS_CMD_MaterialDetails'
LEFT JOIN EGO_ITEM_EFF_B PJTNAME ON ITEM.INVENTORY_ITEM_ID = PJTNAME.INVENTORY_ITEM_ID AND PJTNAME.CHANGE_LINE_ID = CL.CHANGE_LINE_ID AND 300000001488573 = PJTNAME.ORGANIZATION_ID AND PJTNAME.ACD_TYPE IN ('UPDATE', 'ADD') AND PJTNAME.CONTEXT_CODE ='TFS_CMD_MaterialDetails' FROM EGO_ENGINEERING_CHANGES_B C
WHERE C.CHANGE_NOTICE = :CHANGE_NOTICE

Gianni Ceresa · Answer

Absolutely, it's a parallel forum and therefore content can't be moved across the two. Your only option was to post again there. And because that one is the place of the product your data is from, they should have the ability to provide you a valid answer.

Ramsha Zameer · Answer

Thanks, I have posted a question in this category.  @Gianni Ceresa

Is this ok?

Gianni Ceresa · Answer

Hi,

The tables you name sounds like Fusion tables.

Even if you use Publisher to report on those tables, Publisher isn't the owner of those tables, they are owned by your Fusion App. Publisher is only the technical solution running the query for you.

You should consider posting in the forum covering Fusion applications, as they owns the tables you have an issue with:

https://community.oracle.com/customerconnect