Archives of the TeradataForum
Message Posted: Tue, 12 Nov 2002 @ 13:07:44 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|