Archives of the TeradataForum

Message Posted: Sat, 28 Jun 2003 @ 09:38:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

No.

The syntax for a table-level collect statistics:

COLLECT STATISTICS ON db.tbl;

will cause the collection of statistics for only those columns (and/or indexes) which have been *previously* collected. If the table has just been created, then a COLLECT STATISTICS at the table-level will execute successfully - but it doesn't actually collect statistics on any columns.

Let's say that you just created a 6 column table, where the primary index consists of the first two columns. At this point, if you collect statistics at the table-level, then nothing is going to happen.

Collect statistics on the first three columns (remember that the table is empty). Even though you collected statistics on the columns that make-up the primary index, this is not the same as collecting statistics on the primary index - that needs to be done as a separate statement. You might note that if an index consists of a single column, then you only need to perform a COLLECT STATISTICS ON COLUMN or a COLLECT STATISTICS ON INDEX - not both.

If you perform a table-level COLLECT STATISTICS at this point, then the statistics for the first three columns will be refreshed. Since columns 4 through 6 were not previously collected, then nothing will be collected for those columns.

When you populate your table, keep in mind that your statistics will reflect the last time they were collected (when the table was empty). After you make changes to your table (whether insert, update or delete), you need to re-collect statistics.

That's the point of the table-level collect statement - it's a convenience. Instead of having to do individual collect statements again, you only need to perform the table-level COLLECT STATISTICS to refresh those columns/indexes that were previously collected. There's no performance gain when using the table-level COLLECT over the individual COLLECTs (other than the time it takes to parse the individual statements - which is trite and not worth considering).

	<Prev	Next>		<<First	<Prev	Next>	Last>>