Collect Statistics on Empty table

Tools
Enthusiast

Collect Statistics on Empty table

What will happen if we collect statistics on an empty table?
Do we face any complexities because of this?
2 REPLIES
Enthusiast

Re: Collect Statistics on Empty table

Collecting stats on empty table is possible.

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.

Enthusiast

Re: Collect Statistics on Empty table

Hi Smarty,

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-

Satish,

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.

Regards,
KB

Dear Satish

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

Sheila