From TD docs:
Dynamic row partition elimination (DPE) is a form of partition elimination that cannot occur until data values in the tables are known in order to eliminate partitions: Teradata Database cannot determine the partitions to be eliminated until the plan is executed and the data is scanned. Because of this, dynamic partition elimination must be applied on the AMPs after query optimization has already taken place. You can also see " enhanced by dynamic partition elimination "
You read the documents:
Few notes : dynamic row partition elimination is not supported with merge join methods for multilevel partitioned tables
Thanks for the info,still i am not clear can you give one example.When DPE will occur and when static partition elimination will occur.
Thanks in advance
Dynamic row partition elimination improves performance when a partitioned table and a non partition table are joined by an equality condition on the partitioning column set of the partitioned table.
Following link shed detailed info on how it is used and performance advantages: