Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 22 Dec 2009 @ 22:20:51 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016