cancel
Showing results for 
Search instead for 
Did you mean: 

DISTINCT_VALUES Parameter default to GROUPBY

Former Member
0 Kudos

We are looking for a way to default a number of global Universe parameters specifically DISTINCT_VALUES automatically set to GROUPBY for each new Universe generated for a given Teradata DBMS. I reviewed the PRM files for the DBMS in question, but I didn't see the DISTINCT_VALUES listing in the Parameters file.

Any ideas would be greatly appreciated.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

You are asking for a way to default this permanently, rather than changing it inside of Designer itself?

Former Member
0 Kudos

For all new Universes, yes, we would like to change the default to GROUPBY at least for Teradata DBMS. We would still like the developer to have the ability to override in Parameter List for a given Universe, however, a better default is needed.

Former Member
0 Kudos

I am not sure if you can change it as a default setting or not but if you are using Teradata 13 then you should not worry about it as TD will automatically rewrite Distinct to Group By.

- Noman Jaffery

Former Member
0 Kudos

It does not automatically rewrite in our environment. Maybe there is a setting configured differently or something.

Former Member
0 Kudos

Dave,

It will not use Group By in all the cases. If my understanding is correct then Distinct still performs better when the size of table is small. Group By takes over when the table size is huge. This is what the TD magazine states:

GROUP BY and DISTINCT planning enhancements

This feature unifies GROUP BY and DISTINCT, thus removing performance discrepancy between them. In addition, all aggregation operations are incorporated into cost-based join planning, enhancing the Optimizeru2019s ability to pick optimal plans. The innovation significantly improves aggregation performance, which is key to data warehousing applications. It provides the infrastructure for continuing enhancements in the aggregation area.

I will try to find if there is any configuration setting for it or not.

- Noman Jaffery

Former Member
0 Kudos

We only just upgraded to Teradata 12, it could be quite some time before we are upgraded to Teradata 13. Additionally we just upgraded to XIR3SP2, and it does not have Teradata 13 middleware option and so we would need to upgrade again if there is a release avaiable with Teradata 13. We are not likely to do another upgrade this year.

Any other ideas? We are hoping to implement something universal, but not complicated. I am hoping there are some universal parameters list file we can modify to control the defaults from our end.

Former Member
0 Kudos

We do it on each universe. It's not that hard, and it takes only a few minutes. We are working on a "new universe" guide and this is one of several steps we take now for each new Teradata universe.

Even if you found a way to do it via the parameter file, you would still have to update it on every developer system.

Former Member
0 Kudos

Agreed.

Also I did check and can confirm that there is no specific setting for the new Group By/Distinct feature to be enabled in TD 13. It is the default behavior now.

For small tables it will still use Distinct which is more efficient. When the size of the tables is huge only then Group By will be used. I get the same Explain plan for both the options.

- Noman Jaffery

Former Member
0 Kudos

I agree that the parameter change is extremely easy to do, but again its not getting done. We are getting a push by upper level management to provide hard defaults that make sense. GROUPBY is one of several that we are having concerns about. We don't have Teradata 13 and won't have for quite a long time and it won't handle some of the other defaults we need to define as well.

Also, we have always been aware that Distinct performs better on small tables than Group By, but the positive benefit is very minor as the additional processing incurred by Group By method for small tables is minute as the table is small. Distinct on very large tables has been extremely bad for us, hence this inquiry on changing the defaults.