The date function used to display month name from Date column in Teradata

Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.

I think that Teradata will be able to produce this view efficiently. From what you say, it appears to do little more than join some very small dimension tables against a fact table. The join operations will be relatively efficient. Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.

Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.

Beyond that, all I can suggest is that you suck it and see. If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.

I believe Teradata supports window functions, so you can use them to pick the larges value.

select name, age
from (
   select name, age, 
          dense_rank() over (order by age desc) as rn
   from MyTable
) t
where rn = 1;

If there are multiple names with the same age, all would be shown. If you only want to pick one, use row_number() instead of dense_rank()

Using window functions is very often faster than sub-selects.