When we want to access from Core layer double historization tables , such as (valid from --valid to && start_from -- end_to) , how teradata handles this situation.
If the view that we use is really complex and it included a lot of tables , the estimation is totally wrong. Because we access the tables with two between periods ( > < in explain) where we have no confidence.This leads ,if we have more that 150-200 steps , to totally wrong estimations so probably wrong order/duplication/restrubution of execution.
How can we handle this situation? Adding single stats on these 4 columns?Multicolumns stats? Value ordered indexes? Or , create volatiles with the desired result && pi wanted for the join && stats? Is this a good approach?
We do not use the datatype period for these? Is there any improvenent by using these datatype in column?
Until now, i am really dissapointed on how teradata handles this situation , it is like we leave parser to go blind and we just add some stats in order to avoid some situations?