I have a scenario where I will have to design a SQL in my ETL job to retreive rows from a PPI table.The PPI is defined on a date column. As per the schedule my ETL Job runs every 15 days and I will have to retreive the data for the 15 days duration. I have a driver/reference table which gives me the latest 15 dates OR I can hard code the dates in my SQL,Since the dates are known.
My question is, which approach will be better ,performance wise? When I hard-code the dates, The partitions to be accessed will be determined in parser phase itself. If I Join the reference table to the fact table, DPE will be done during run time.
Out of the 2 methods, which is advisable?
What is the performance overhead(if there is any) in DPE method?
when you are using a driver table for peeking dates, the DPE will generate a "product join enhanced by dynamic partition elimination.....". Regardless on how mush data you have in the table, this could be an issue
ON TD12, the built-in function current_date is now processed on parsing phase, so an expression like will fire the Partition Elimination, it could be a good solution.
for V2R6.x, the best methode for the partition elimination is to hardcode dates.
Agreed that it is advantageous to Hardcode the dates so that parser knows what partitions it is going to access.But, When we have dates driven by a reference table, How bad (or Good) is Dynamic Partition Elimination ? Does Teradata Optimizer pick a full table scan if it finds DPE is expensive? Does anybody know a scenario where DPE is expensive?
For that case, How does the Product Join enhanced by DPE work?
When you are using a little driver.lookup table which contains the values of the partitionning column, and if there is a small number of distinct values (in my clients experiences less than 100, but it depends on volume of the large table), Teradata duplicate the small table, then perform aproduct join against the large table which is PPI, During the join process, DPE skip partitions which are not candidat, and jump to the next partition.
BTW, if you're on TD12 , using a dynamic predicate like :
Where PPI_DATE_COLUMN between Current_Date and Current_Date - interval '15' day
will perform the Partition elimination during the parsing phase.