All Forums Database
Tnewbee 215 posts Joined 05/10
30 Sep 2010
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!

dnoeth 4628 posts Joined 11/04
30 Sep 2010

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

Dieter

Jimm 298 posts Joined 09/07
01 Oct 2010

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.

You must sign in to leave a comment.