I have encoutered a weird problem when using single table Join Index. Basically when I want to join to a table (based on which has built a single table JI) twice in a single query, Teradata only use the JI once and redistribute the base table for the second join.
Here is the sample of my query:
table A ( Col_X, Col_Y, Col_Z ) primary index (Col_X);
table B ( id, name ) primary index (id);
Join Index B_JDX ( name, id ) primary index (name);
select Col_X, b1.id as Col_Y_id, b2.id as Col_Z_id from A join B b1 on A.Col_Y = b1.name join B b2 on A.Col_Z = b2.name ;
The reasonable Explain that I expect to get is that it should always redistribute the table A to join with the B_JDX. However it chose to use B_JDX once and use B for the other. Could anyone please help shed some light on any possible reason for that?