Forum Stats

  • 3,855,365 Users
  • 2,264,500 Discussions
  • 7,905,979 Comments

Discussions

Update is running long with temp transformation in execution plan

2»

Comments

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond


    Unfortunately the _with_subquery parameter isn't considered to be an optimizer parameter. And until 19.3 I can't seem to get the inline() hint to apply to a query block other than the one it's in, otherwise that would be something you could use to override the parameter effect.

    About the online thing you could do, perhaps, is to run the query with the parameter set, copy the set of hints produced in the OUTLINE information, and then write that set of hints into the SQL. (Which would work if the only difference in the statements was some injected literal).

    Regards

    Jonathan Lewis

  • GregV
    GregV Member Posts: 3,099 Gold Crown

    Hi,

    When I need to perform an UPDATE whose SET clause is going to be using the same subquery as the one in the WHERE clause, I tend to go for a MERGE statement then. The rows of interest will be selected in the query within the USING clause, so the rows are read only once. Your statement could be rewritten this way (not tested, so there may be typos):


    MERGE INTO COMSYS.BRANCH_BONUS_DETAIL A
    USING (SELECT B.REP_ID,
                 B.REP_LAST_OSJ_SWTCH_DT
           FROM COMSYS.REP_OSJ_SWITCH_VW_BRANCH_BONUS B
           WHERE REP_LAST_OSJ_SWTCH_DT >= :B2
            AND EXISTS ( SELECT
                           'x'
                         FROM
                              COMSYS.REP R1
                         WHERE
                              R1.REP_ID              = B.REP_ID
                         AND    R1.MASTER_BANK_ID IS NULL
                         AND    NVL(R1.MASTER_ID, R1.REP_ID) NOT IN
                                 (
                                         SELECT R2.MASTER_BANK_ID
                                           FROM
                                                   COMSYS.REP R2
                                           WHERE
                                                   R2.MASTER_BANK_ID IS NOT NULL))                                               
    
         ) v
    ON (V.REP_ID = A.REP_ID
       AND A.PAY_PERIOD = :B1)
    WHEN MATCHED THEN UPDATE SET A.EXCLUDE_FLAG = 'Y',
                                A.REASON      = A.REASON || '5 - CHANGED OSJ ON ' || V.REP_LAST_OSJ_SWTCH_DT || '|', = 
                                A.LAST_OSJ_DATE = V.REP_LAST_OSJ_SWTCH_DT;
    
    AndrewSayer