How to tune this query?

Question

I am calculating the resource cost:

        SELECT NVL(sum(WT.TRANSACTION_QUANTITY* WTA.RATE_OR_AMOUNT),0) 
        --    INTO l_resource_cost
        FROM APPS.WIP_TRANSACTION_ACCOUNTS WTA, APPS.WIP_TRANSACTIONS WT
        WHERE WTA.TRANSACTION_ID = WT.TRANSACTION_ID
        AND WTA.ORGANIZATION_ID = WT.ORGANIZATION_ID
        AND WTA.ACCOUNTING_LINE_TYPE =7
        AND WTA.WIP_ENTITY_ID = 1757957
        AND wt.operation_seq_num = 10;

The tables are Oracle ebs WIP tables, the data is correct but this is need to be executed for thousands of records.

How can I tune it?

can I attache the explain plan here?

Answer 1

First, learn to use proper explicit JOIN syntax. Simple rule: Never use commas in the FROM clause:

SELECT COALESCE(sum(WT.TRANSACTION_QUANTITY * WTA.RATE_OR_AMOUNT), 0) 
        --    INTO l_resource_cost
FROM APPS.WIP_TRANSACTION_ACCOUNTS WTA JOIN
     APPS.WIP_TRANSACTIONS WT
     ON WTA.TRANSACTION_ID = WT.TRANSACTION_ID AND
        WTA.ORGANIZATION_ID = WT.ORGANIZATION_ID
WHERE WTA.ACCOUNTING_LINE_TYPE = 7 AND WTA.WIP_ENTITY_ID = 1757957 AND
      wt.operation_seq_num = 10;

Indexes will help the query. I would recommend:

• APPS.WIP_TRANSACTION_ACCOUNTS(ACCOUNTING_LINE_TYPE, WIP_ENTITY_ID, RATE_OR_AMOUNT)
• APPS.WIP_TRANSACTIONS(TRANSACTION_ID, ORGANIZATION_ID, operation_seq_num, TRANSACTION_QUANTITY).