Archives of the TeradataForum
Message Posted: Tue, 22 Dec 2009 @ 22:20:51 GMT
| Subj: | | Re: Problem using join index. |
| |
| From: | | Victor Sokovin |
| | I have got the JI working for your view after I added a row ID from the Left Table (,AK.ROWID AS AK_ROW) on your JI. I am not sure if
it is the calculated field that was causing the issue. However I can confirm JI is working using /coming through the view and Joins both the
Inner and Outer inside the view. | |
First of all, it is good to see the DDL so that the issue can be discussed more specifically. Thanks go to the OP who did not leave our
requests unanswered.
The above solution provides some insight in what might be the problem with the optimizer in the case when the views are used. I carefully
scanned the DDL and, as far as I can tell, the JI can provide the full coverage of the query. However, the optimizer does not seem to be able to
figure that out.
When AK ROWID is included in the DDL of JI, the optimizer probably decides that there is partial coverage and it may still use the AK table as
well.
In other words, my guess is that the optimizer will use the modified JI and perform ROWID join to AK. ROWID-based join in this case should be
fast enough not to notice any performance problems. So, on the practical level, the solution should work.
If we don't overlook something unusual in the definitions, I'd say this is a bug. The view-based query should be fully covered by the original
JI without any modifications. Submitting a bug report to Teradata would be in order.
Victor
| 
