Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
13
Views
3
Comments
Ramsha Zameer
Ramsha Zameer Rank 2 - Community Beginner
edited April 14 in Oracle Analytics Publisher

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"

FROM EGO_ENGINEERING_CHANGES_B C

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'

WHERE C.CHANGE_NOTICE = :CHANGE_NOTICE

Tagged:

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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

  • Ramsha Zameer
    Ramsha Zameer Rank 2 - Community Beginner
    edited April 14

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

    Is this ok?

    image.png
  • Gianni Ceresa
    Gianni Ceresa Mod

    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.