Quick code tune required

[Horace]

Hi code gurus,

Quick help required....

Is there a better way to code this..... Many thanks

update trans t 
 set t.cmp = 'Feb' where exists  
   (select *   from
    CMPFebTest c   
    where  c.account_number = t.account_number   
    and     t.effective_date  <= c.End_Date   
    and     t.effective_date >= c.contact_date)

[pando]

have you tried update view?

[tabreaz]

UPDATE (SELECT t.cmp 
            FROM trans t, CMPFebTest t
            WHERE  c.account_number = t.account_number   
            AND t.effective_date  <= c.End_Date   
            AND t.effective_date >= c.contact_date
	    )
SET t.cmp = 'Feb'

--OR

MERGE INTO trans t
USING (SELECT account_number
	FROM CMPFebTest 
	WHERE t.effective_date  <= c.End_Date   
	AND     t.effective_date >= c.contact_date
	)c
ON (t.account_number = c.account_number)
WHEN MATCHED THEN
UPDATE SET t.cmp = 'Feb'

[yxez]

Hi Horace,....feedback pls if the performance improved with
the rew code

[Horace]

Oops. Sorry Guys.

Tabreaz, thanks very much for those alternatives.

To be honest, I was maxed out when this problem arose. One of the developers had been running this on one of our many databases for 20 hours, and still running. When I finally got to looking at it, the PLAN was horrendous with a FILTER in it. Noticed that neither table was ANALYZED.

So quick fix, and the code ran in under 30 seconds!! The FILTER was replaced with HASH JOINS, once the CBO had a clue.

Thanks again