I have three tables A, B and C. Table B is a bridge/ relationship table having Ids from table A and C. A and B have the same primary index. Table C has a different primary index.
I am joining them like this in a view
INNER JOIN B
ON A.Id1 = B.Id 1 (Id1 is primary index for both A & B)
INNER JOIN C
ON B.Id2 = C.Id2 (Id 2 is primary index for C)
AND C.someothercolumn = 'some value'
Now when i do a query on top of this view with a condition like A.date = '2013-05-25' and see the explain plan, I see that optimizer starts with table B, it redistributes B on Id2 to have the same index as C. Then joins B and C. Then it redistributes the result on Id 1 and then joins it to A .
This results in 2 redistributions, which is inefficient. What I want optimizer to do is to join A & B first without any redistribution (as they have the same PI) and then redistribute the result just once to Id 2 ( PI of C) and join it to C . So the redistribution will occur just once . Not twice.
How do I accomplish that?
The optimizer is doing the B JOIN C first, probably because this will reduce the number rows upfront. Check the stats on the tables to confirm if they are up to date.
hi, im also interested in this.would there be any kind of query rewriting technique that can be used to force teradata doing what kind of joins we wanted first before the others? or the only way is via redistribution?