Query on efficient join and update

Database

Query on efficient join and update

Hi Experts

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.

Thanking You

Santanu

3 REPLIES
N/A

Re: Query on efficient join and update

Hi Santanu,

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?

Re: Query on efficient join and update

Hi Dnoeth

Thanks for your reply. Below example might give you some idea what we are trying to do.

CUST

----------

CUSTID CUSTNAME ORDRID ORDRDT

1 SAM O1 SOME_DT1

2 SAM O2 SOME_DT2

3 TOM O3 SOME_DT3

CAL

--------

DATES WEEKPRD

SOME_DT1 SOME_WK1

SOME_DT2 SOME_WK2

SOME_DT3 SOME_WK3

SEL CUSTID, CUSTNAME, ORDRID, WEEKPRD

FROM CUST, CAL

WHERE ORDRDT = DATES

;

Thanking You

Santanu

N/A

Re: Query on efficient join and update

Hi Santanu,

what's the number of rows in your calendar?

You should trust the optimizer, can you show the Explain?