The explain plan which we see in teradata by using command like EXPLAIN < QUERY> is estimated plan or actual plan ?
if it is estimated plan then where is actual plan ?
Ohh...is it ?????
I was under the impression that EXPLAIN <QUERY> shows an estimated plan that can vary in actual may be because of stats and other things. and the actual plan can be seen in DBQL.
That is true through TD14.0.
In TD14.10 and later, there is IPE (Incremental Planning and Execution) feature which may result in the actual plan being different from the default (STATIC) EXPLAIN. In 14.10 and 15.0, the actual plan differs only for certain queries with single-row / non-correlated scalar subqueries, and atyou can see the "actual" plan at a point in time by requesing DYNAMIC EXPLAIN <query> (which runs the subqueries at EXPLAIN time and uses the current results in conjunction with stats / sampling to plan the rest of the query).
And realize that at best, EXPLAIN will tell you how the query would be executed if you ran it now. But if the stats, data, etc. change significantly then it may run differently now than it did yesterday or than it will tomorrow. So for diagnostic purposes, DBQL provides options to capture the explain text or an XML representation of the plan at run time.