Archives of the TeradataForum
Message Posted: Fri, 14 Apr 2000 @ 23:40:33 GMT
Time Dimension TableDepending upon what you use the system calendar view to do you can run into some significant performance problems. The reason is that it is, as John Dubery said, a single column, with calculations layered in views to get the remaining columns. This means that you can't collect statistics on the other columns to which you might like to join, or upon which you would like to constrain. You can't create secondary indexes on them either, since they are derived. If you are dealing with multiple dates, or ranges of dates, you wind up with a pretty ugly explain and long run times compared to creating a table. Therefore, I strongly suggest instantiation of a table, containing what you really want in it. Then, of course, creating appropriate indexes and collecting statistics.
NCR suggested in a V2 R3 class that you create an additional view for today that selected all the columns from the system calendar view where calendar_date = date. That provides a convenient way to get all of the columns of information from a single row view for today's date, and that works quite well.
Without more information about how you plan to use dates it would be hard to advise how many years to put into it. What is your retention period for records? How many years into the future do you want to go ahead and populate for convenience? What is the interval of time that really matters to you?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|