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.
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.
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.
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.
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.
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..
It is available in 14.10 version. For further details visit: