Manipulation done on PI

Database
Enthusiast

Manipulation done on PI

Say i have a table with PI on col1 and col2. I have a join condition like :

 table1 A

 LEFT OUTER JOIN table2 H

 ON A.col1 = (

CASE WHEN H.col1 LIKE 'X%'

 OR H.col1 IS NULL THEN 'NO NUMBER'

ELSE H.col1

END  )

 AND A.col2 = H.col2

When you see the explain: it does an all AMP retrive from table2 and then redistributes on col1,col2 and then does

a join with table1.Col1 and Col2 are PI of table1 as well. I am not able to understand, when col1 and col2 is already

a PI of table2 why does it do a redistribution. My guess is: Since we are not using the col1 directly instead we are using

it after some manipulation that might be a reason.

then i cretaed a derived table of table 2

table1

LEFT OUTER JOIN

 (

 SEL

 columns...

CASE WHEN col1 LIKE 'X%'

 OR col1 IS NULL THEN 'NO NUMBER'

ELSE col1

END,

FROM table2

 ) H

 (

column names,col1

 )

 ON table1.col1 = H.col1

 AND B.col2 = H.col

then also it does the same.

Does it mean that if we do any manipulation/create a derived table and then use the PI columns, it doesn't actually use the

PI?

1 REPLY
Junior Contributor

Re: Manipulation done on PI

You're correct, when you do a calculation on the PI column the value might change resulting in the need to redistribute based on the new hash.

When you move the calculation into a Derived Table you still need to calculate the new hash.

Deopending on what you try to achieve moving the CASE from the join condition to the select list might help:

SELECT

  CASE WHEN H.col1 LIKE 'X%'

   OR H.col1 IS NULL THEN 'NO NUMBER'

  ELSE H.col1

  END

FROM table1 A

 LEFT OUTER JOIN table2 H

 ON A.col1 = H.col1

 AND A.col2 = H.col2

Dieter