But suppose U have collected stats on empty table and after that u loaded data in to that table and try to executed any select operation then optimizer will not found any supportive stats so will go for dynamic sampling.
So it is required to refresh u'r stats as time moves.
You should collect statistics using the Optimizer form on newly created data tables while they are still empty. An empty collection defines the columns, indexes, and synoptic data structure for loaded collections. You can easily collect statistics again once the table is populated for prototyping, and again when it is in production
What will be the impact/Process when we collect stat after loading the table (For the empty table which has stats already) --
To update demographics automatically, enter the COLLECT STATISTICS statement with only the tablename (omit column or index specifications): COLLECT STATISTICS ON table-name ; The parser then does as follows: • Determines what columns or indexes of the table already have a statistics structure in the data dictionary. • Re-collects statistics on those columns and indexes in the same node (SAMPLE or not) as previously collected.
Below are few Replies from Experts-
Stats are not automatically refreshed. Not sure about TD13 though. I would recommend you not to make this a practice, because stats on empty table would mean distinct counts by optimizer would be recorded as 0 or 1. If this table is supposed to be a very big table, then after loading, when you query or join this table to others, optimizer would most likely duplicate this on all amps thinking its a very small table. Depending on how big your system is, you may end up seeing a TB of spool for your running query etc. So, even if you have collected stats on an empty table, recollect after load.
Yes one can collect statistics on an empty table. Some people do this as a matter of course and then have a COLLECT STATISTICS ON tableX; clause that recollects all defined stats after the load as been load often as a final step in a multiload job. However, the consequences are IF the stats are not recollected then the optimiser is likely to get a wrong plan - and stats are not automatically recollected!
Thus do not collect stats when the table is empty if there is a chance they will not be recollected -this is certainly true pre Teradata 12