When we need to collect stats on table

General
Enthusiast

When we need to collect stats on table

Hi,

   we are loading data into teradata through informatica using TPT load connection.Our load type is truncate and reload every month.

1)Do i need to collect the stats on the table after load?If yes how i can collect the stats,bcoz TPT connection won't support Pre/Post SQL.

2)On what columns we need to collect the stats(dont hav clear information how table will be used)

Pls giv ur advices on it.

Thanks

6 REPLIES
Teradata Employee

Re: When we need to collect stats on table

We had a similar scenario in one of the implementations  .... and we had two options:

- to create a BTEQ script for collecting stats and call it as a last task in Informatica

- to revert to ODBC connection [or if PDO is available]

And considering the tables data will be changed considerably, yes you should ideally collect stats on tables which are used more frequently or if the usage is not known for the tables which are too huge or too small in size. It should give fair-enough information for a good query-path selection.

Teradata Employee

Re: When we need to collect stats on table

In case columns usage is not known, just collect the stats on index columns.

If possible, try to find out the logical PKs of the tables which might be used for joining and getting the required information .... those columns can also be included in collecting stats.

Enthusiast

Re: When we need to collect stats on table

Usually, collect stats is done at the end of load or collectively at the end of all loads. In real time big DWH projects, we collect stats only for important tables. In case, the architecture has work, staging tables ,temporary tables etc in most cases we dont collect. Collecting stats takes time when it collects full stats on a table. In case you find time consuming you can resort to using sampling too.

I think your tables are not partitioned.

Teradata Employee

Re: When we need to collect stats on table

There is a lot of material by Carrie Ballinger here on DevX which provides recommendations on where and when to collect stats. I suggest starting there.

Enthusiast

Re: When we need to collect stats on table

Hi Experts,

I was looking into teradata 14 new features from TEN, i found that there is teradata Autocollect stats feature, which takes care of missing/stale stats automatically.

Do we need to activate it through some some flag or its inbuilt by default.

Please correct me if i am wrong..

Cheers!

Nishant

Teradata Employee

Re: When we need to collect stats on table

It is available in  14.10 version. For further details visit:

http://developer.teradata.com/blog/carrie/2013/12/easing-into-using-the-new-autostats-feature