How to modify an update in Oracle so it performs faster?

I have this query:

UPDATE   (
    SELECT   h.valid_through_dt, h.LAST_UPDATE_TMSTMP
    FROM   ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h
    WHERE       h.FUND_ID = d.FUND_ID
    AND h.FEETYPE_NAME = d.FEETYPE_NAME
    AND h.BREAKPOINT_TYPE = d.BREAKPOINT_TYPE
    AND h.BREAKPOINT_QTY = d.BREAKPOINT_QTY
    AND h.LOW_BREAKPOINT_AMT = d.LOW_BREAKPOINT_AMT
    AND h.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy')
    AND h.universe = 'DC'
    AND h.universe = d.universe
    AND EXISTS
    (
        SELECT 1
        FROM FEE_SCHEDULE s
        WHERE s.FUND_ID = h.FUND_ID
        AND s.FEETYPE_NAME = h.FEETYPE_NAME
        AND s.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE
        AND s.BREAKPOINT_QTY = h.BREAKPOINT_QTY
        AND s.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT
        AND s.universe = 'DC'
    )
) updateTable
SET     updateTable.VALID_THROUGH = (SYSDATE - 1),
updateTable.LAST_UPDATE_TMSTMP = SYSTIMESTAMP;

The trouble that I am having is that this query takes a long time to run. I don't know whether it is possible to run this on parallel, or it would be easier to update a cursor in a pipeline function.

What would you suggest?

This is all the information that I believe it is relevant.

This is the execution plan of the internal select:

```
Execution Plan
----------------------------------------------------------
Plan hash value: 57376096
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 306 | 8427 (1)| 00:01:42 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 306| 8427

I'm assuming that your FEE_SCHEDULE table is a lot smaller than the FEE_SCHEDULE_HISTORICAL table, so you might want to exploit EXISTS a little more. Dipping into the FEE_SCHEDULE table on a row-by-row basis can be a relatively cheap operation compared to joining it to all the rows in the FEE_SCHEDULE_HISTORICAL .

Since the ETL_FEE_SCH_TMP table is the FEE_SCHEDULE_HISTORICAL minus FEE_SCHEDULE, you can actually accomplish the update with just a couple of EXISTS statements, without all the joins and saving you the trouble of creating the temp table. You don't really need the temp table.

I think this might be worth a look :

update FEE_SCHEDULE_HISTORICAL H
set H.VALID_THROUGH = (sysdate - 1), H.LAST_UPDATE_TMSTMP = SYSTIMESTAMP
where 
    H.VALID_THROUGH = TO_DATE ('31-DEC-9999', 'dd-mon-yyyy')
    AND H.universe = 'DC'
    AND NOT EXISTS
    (
    SELECT 1
        FROM FEE_SCHEDULE F
        WHERE 
            F.universe = H.Universe
            AND F.FUND_ID = H.FUND_ID
            AND F.FEETYPE_NAME = H.FEETYPE_NAME
            AND F.BREAKPOINT_TYPE = H.BREAKPOINT_TYPE
            AND F.BREAKPOINT_QTY = H.BREAKPOINT_QTY
            AND F.LOW_BREAKPOINT_AMT = H.LOW_BREAKPOINT_AMT
            AND F.HIGH_BREAKPOINT_AMT = H.HIGH_BREAKPOINT_AMT
            AND F.FEE_PCT = H.FEE_PCT
    )
    AND EXISTS
        (
        SELECT 1
        FROM FEE_SCHEDULE FF
        WHERE 
            FF.universe = 'DC'
            AND FF.FUND_ID = h.FUND_ID
            AND FF.FEETYPE_NAME = h.FEETYPE_NAME
            AND FF.BREAKPOINT_TYPE = h.BREAKPOINT_TYPE
            AND FF.BREAKPOINT_QTY = h.BREAKPOINT_QTY
            AND FF.LOW_BREAKPOINT_AMT = h.LOW_BREAKPOINT_AMT
  )

Also, consider adding an index to the FEE_SCHEDULE table, similar to the one on the FEE_SCHEDULE_HISTORICAL. That really helps the explain plan along.

CREATE UNIQUE INDEX FEE_SCHDL_IDX ON FEE_SCHEDULE (
    UNIVERSE,
    FUND_ID,
    FEETYPE_NAME,
    BREAKPOINT_TYPE,
    BREAKPOINT_QTY, 
    LOW_BREAKPOINT_AMT
);

StackExchange Database Administrators Q#25257, answer score: 2