构建数据仓库时的 Varchar2 与 date
在 Oracle 中构建数据仓库时,选择 varchar2 而不是日期数据类型有什么区别吗?我的数据仓库不是完全仓库,因为不需要日期维度(不需要日期层次结构),将日期保存为普通的“MM-DD-YYYY”格式的字符串就足够了。然而要使用哪种数据类型?
Does it make any difference to prefer varchar2 over date data type when building data warehouse in Oracle. My data warehouse is not totally warehouse because date dimension is not necessary (no need for date hierarchy) and it is enough to save date as plain 'MM-DD-YYYY' formatted string. Which data type to use nevertheless?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
让我们做一些成本/效益分析,好吗?
将“日期”字段保留为
VARCHAR2
优点:
MM-DD-YYYY
格式显示)。缺点:
TO_DATE
调用,搜索计算量增加TO_CHAR(TO_DATE(date_column, 'MM-DD-YYYY'), 'DD-MON-YYYY')
)TO_DATE
,则会增加失败的风险。将“日期”字段更改为
DATE
优点:
TO_DATE
开销的情况下进行搜索缺点:
希望这将从技术角度帮助您做出特定决定。总有商业(或办公室政治)视角:
我会为您解决这个问题:
假设您将列保留为
VARCHAR2
,并且客户端出现并要求过滤此日期字段的某些报告。假设这会杀死数据库服务器,并且您最终需要将其转换为DATE
。在贵公司的生产环境中进行此更改相对容易吗?是否有大量的繁文缛节、表格和变更控制委员会是您必须努力完成的简单变更?如果您确实设法克服这些障碍,您确定所有日期都以相同的格式存储吗?Let's do a little cost/benefit analysis, shall we?
Leave the "date" field as
VARCHAR2
Pros:
MM-DD-YYYY
format).Cons:
TO_DATE
callsTO_CHAR(TO_DATE(date_column, 'MM-DD-YYYY'), 'DD-MON-YYYY')
)TO_DATE
is necessary.Change the "date" field to
DATE
Pros:
TO_DATE
Cons:
Hopefully this will help in your particular decision from a technical perspective. There is always the business (or, office politics) perspective:
I will fix that for you:
Suppose you leave the column as
VARCHAR2
and the client comes along and asks to filter some report on this date field. Suppose this is killing the database server and you finally need to convert it toDATE
. Is it relatively easy to make this change in a production environment at your company? Is there plenty of red tape and forms and change control boards you must fight to make your simple change? If you do manage to overcome those hurdles, are you sure that all dates are stored in the same formatting?这里的“足够”是什么意思?我可以看到不使用 DATE 会带来什么损失,但是您会得到什么,值得考虑呢?当然,如果您持有日期值,在某些时候您会想要使用它,例如“2011 年 3 月的销售额”或其他什么?
What does "enough" mean here? I can see what you lose by not using DATE, but what do you gain that makes this worth considering? Surely if you are holding a date value, at some point you will want to use it e.g. "sales in March 2011" or whatever?
应避免使用 varchar2,因为它占用空间,为什么要使用比不占用更多空间的数据类型呢?
也许如今磁盘存储空间不再是一个大问题,但它可以节省一些 RAM 空间,防止磁盘 I/O,特别是在频繁访问包含这些日期的记录时。
如果检索记录时需要磁盘 I/O,则速度会稍快一些,因为使用 date 与 varchar2 相比,大小更小。
varchar2 should be avoided because it takes up for space, why use a datatype that takes up much more space than one that does not?
Perhaps disk storage space is not much of an issue these days, but it can save up some RAM space, preventing disk I/O, especially if records containing those dates are accessed frequently.
And if disk I/O is required when retrieving a record, it would be slightly faster because the size is smaller when using date vs varchar2.