I have two requirements. Need your help to solve them for project.
1. there is TableA with 10M rows and 50 cols. Need to update only 3 cols out of them for 1M rows. Will a single update query be efficient or there is a better way?
2. There is fact table Cust with 10M rows and custid as PI and among other cols there is one date col dt_col. Then there is calendar table Cal with C_Dt as PI. If we join Cust and Cal on those date cols dt_col and c_dt , possibility is Cust table will be redistributed on dt_col for merge join. Any STJI or MTJI cannot be created. In that case what best can be done within the query to improve this, so that the 10M rows redistribution does not happen.
Please, please help me out.
Q1: Of course a single UPDATE works best.
Q2: Based on statistics the optimizer should choose the best plan, the alternative will be duplicating rows from the calendar table to all AMPs. If the number of rows in the calendar table is low this might be more efficient, can you add a WHERE-condition on the calendar?
Thanks for your reply. Below example might give you some idea what we are trying to do.