Collecting Stats after deleting all data

Database
Enthusiast

Collecting Stats after deleting all data

HI all!

I have seen this code and I am coing on the same lines.
But should we collect stats after we delete all the data in a table?

This is how code looks:
DELETE FROM table1;
Collect Statistics table1 index(.....);
Collect Statistics table1 coulmn(.....);

I understand collecting stats after deleting large amounts of data, but whats the use of collecting stats on an empty table?

Thanks!
2 REPLIES
Senior Apprentice

Re: Collecting Stats after deleting all data

It's the same reason as any stats, it tells the optimizer about the number of rows and helps producing a better plan.
And there's almost no overhead collecting stats on a an empty table.

But if the next step is an insert/select you don't need stats for that.
Hopefully there is another collect stats after the table is inserted again.

Dieter
Enthusiast

Re: Collecting Stats after deleting all data

It could be a historical thing.
In older Teradata releases, you could Collect Statistics on a table (without specifying columns or indices) and it would recollect all defined stats in a single pass. So it was quite common to define stats on the table, populate it, then Collect Stats on the table.
In more modern Teradata (V2R5 onwards), Collect Stats on the table will collect stats on each column set or index individually, so it takes a while.