Issue with using single table JI

Database

Issue with using single table JI

Hi,

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?

Thanks!

2 REPLIES
Junior Contributor

Re: Issue with using single table JI

Hi Richard,
AFAIK this is a known limitation of JIs, the optimizer is using them only once within a query.

Dieter

Re: Issue with using single table JI

Thanks for sharing your insights, Dieter!

Is there any reference or material for the reason of that limitation? Or is there any work around it in TD13?

Thanks,
Richard