 |
 |
Archives of the TeradataForum
Message Posted: Fri, 14 Apr 2000 @ 23:40:33 GMT
| Subj: | | Re: Time Dimension Table |
| |
| From: | | Sam Mosley |
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?
Sam Mosley
President VLDB Systems, Inc.
| |
