Impact of timestamp as join key instead of integers

[SteveCSC]

Can a DBA (or anyone) give me a general idea of the impact of using a timestamp as a join key between the time dimension and a fact table, instead of integer values (three separate columns - day, mo, yr)? Also, is there is difference for indexing the the three columns were text instead of integer?

 

[kishleo]

i dont suggest you to have a timstamp to join the tables, why are you using 3 columns to join to fact table, what is the structure of your time dimension, you just have to join the fact table and time dimension based on the sorrogate key in time dimention, i mean each and every date in time dimention will be represented by one unique value as a key to dimension while populating the fact table you have to refer the time dimention for particular date and papulate the key from time dimention to fact table so that later it will be easy for you to join the tables, dont store dates in fact tables,

ô¿ô Kishore

 

[SteveCSC]

THank you - your comments have been very helpful!

 

[bruceps]

Instead of using 3 separate columns for the date, what I've been using is a single column
to_number(to_char(sysdate,'yyyymmdd'))
to provide a column more amenable for use an index.
You may want to give it a try and do a speed comparision.