Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 12 Nov 2002 @ 13:07:44 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Soft RI and LTST Optimisation
From:   Matthew Winter


I have been looking at the Large Table Small Table (LTST) optimisation, where several small tables are cross joined together, before joining to the large table.

We have an example of a large table (approx 200 million rows) joining to 4 small tables (each approx 1000 rows).

9 times out of 10 filters are placed on these tables, when they are used, and on average 2 of these tables are used in a query.

When a filter is applied the number of records in the resultset are reduced down to 1 to 7.

Based on this, the LTST optimisation is an excellent way of improving the query performance.

If for example we distributed the large table on the 4 foreign keys of the 4 small tables, LTST optimisation will only be performed when all 4 small tables are part of the query. As you can see, this is not always the case.

Looking at the Soft RI feature within V2R5, would it not be possible to specify the Soft RI relation between the foreign keys of the large table, and the 4 small tables, then when a query comes through without all 4 small tables joined, the optimiser could perform the cross join to the missing table(s), and allow for a greater usage of the LTST optimisation.

For the above example, having 2-4 joins present, the LTST optimisation would be the more performant choice, depending on the filter selection statistics. Giving the optimiser another path to choose from.


Matthew Winter

Technical Architect
TPG IS Design Authority
Teradata Certified Professional

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