I am new to teradata performance tuningand it would be great if someone please guide me on performance tuning especially the confidence part in EXPLAIN and how to better tune queries in case of LOW or NO CONFIDENCE. What would affect the confidentiality and how does the optimizer classify these? Also some help on COLLECT STATISTICS would be appreciated. Any material on this would also help me a lot.
I would suggest spending some time reading Carrie Ballinger's blog posts on Statistics Collection found here on the Developer Exchange: http://forums.teradata.com/blog/carrie/
Carrie's insight should help get you going in the right direction understanding what statistics should be collected, how often, and why. You can use the SET DIAGNOSTIC HELPSTATS ON FOR SESSION to get some recommendations on what stats "could" benefit your query. There are instances where the suggestions are overzealous and the cost to collect and maintain the stats outweighs their benefits. As always, you should regression test your queries that are affected by the additional stats collected on a particular table or tables.
tuning on query level , basically you should look on joins try to join with the columns which are declared as primary index. then if the joining columns are not indexed the declare it as secondary index. then go for collect statistics.and coming confidence level in explain plan it is said that if it has high confidence the joinings among the tables are well defined and organinzed well in the AMPS to fetch the records.