All Forums General
spkother 18 posts Joined 07/13
15 Apr 2014
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

Adeel Chaudhry 773 posts Joined 04/08
16 Apr 2014

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
16 Apr 2014

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.

-- If you are stuck at something .... consider it an opportunity to think anew.

Raja_KT 1246 posts Joined 07/09
18 Apr 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ToddAWalter 316 posts Joined 10/11
18 Apr 2014

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.

19 Apr 2014

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

Adeel Chaudhry 773 posts Joined 04/08
20 Apr 2014

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.