Why explain plan is so off


Why explain plan is so off

I am new to Teradata. I am doing an explain on a  sql statement

select * from myview

where myview is a join of 10 tables.

I see in last few line of explain

"     statement 1.  The total estimated time is 4,122,259 hours and 25 minutes:.

But when I run the sql statement, it only took 5 minutes. I think there is no statistics on any of the tables.

Still why explain is so far off with the actual execution time.


Re: Why explain plan is so off


You do not have any stats collected on any table, thats the thing misleading the optimizer. The optimizer always require stats on table to give accurate estimates, else it may give unrealistic estimates than actual.


Re: Why explain plan is so off

The optimizer doesn't always needs stats to give estimates. If full stats are collected and available, then the optimzer will give you realistic estimates, else if the full stats are not collected then the optimizer looks for the sample stats, if collected then optimizer uses them and the estimates will be close to actual one. If no stats are collected then the optimizer does the random amp sampling and gives out the estimates which could be close or way unrealistic. This could be ther reason your estimates are very unrealistic.

Collect the stats and then see the explain plan, which will give you a better picture.