Product join and data redistribution


Product join and data redistribution

Hi All,

I am using TD14 version. I have a query, where A table ( large one) is joined with B table (small one), on column A.col1=B.col2. The column col1 is a skewed column having 60% NULL value. col2 of B table is the unique primary index. I am finding the query is going on product join, which is quite expected. But before the joining step, A table (large one) is redistributed on col1. As per my knowledge the table A should not be redistributed, but should be put in memory. And then each row of small table should be compared witht the large table. 

Can any one tell me, why this is happening or this is expected. The statstics are quite fresh and collected on the joining and PI columns.


Re: Product join and data redistribution

Hi Asim,

Table A is getting distributed as it contains 60% nulls and remaining 40 % might be less comparing to Table B data.

Re: Product join and data redistribution

 This is the formula to get product join indicator ,

Case when TotalIOCount = 0 then 0 else (AMPCPUTime * 1000)/TotalIOCount End

Can somebody help me understand why AMPCPUTIME gets multiplied by 1000 here ?

Thanks a lot in advance.