SQL Update:Multi column update with subquery factoring very slow

Hi ,

I have a query which is structured like following,

update tab1 t1

set (c1,c2,c3) =

(

          select c1,c2,c3

          from

                    (

                              with sq1 as (

                                        select ... from tab1 join tab2 ..etc

                                   ),sq2 as (

                                         select ...

                                   )

                                   select

                                                 

                                   from    sq2

                     ) u1

                    where u1.c4 = t1.c4

                    and u1.c0 =  t1.c0 -- assume this is primary key

)

c4 = '1234'; <-- Filter ????

I had to do some good amount of work get this query working but it is very slow. The table has 600K rows currently. When I checked on SQL monitor (11.2) , it is showing that one of the hash joins are sort of creating huge amount of rows 400M..etc.