|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/24/2009 5:42:27 AM
Posts: 2,
Visits: 7
|
|
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!
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
Hi Richard,
AFAIK this is a known limitation of JIs, the optimizer is using them only once within a query.
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/24/2009 5:42:27 AM
Posts: 2,
Visits: 7
|
|
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
|
|
|
|