构建数据仓库时的 Varchar2 与 date

发布于 2024-11-25 09:26:14 字数 131 浏览 3 评论 0原文

在 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

夏至、离别 2024-12-02 09:26:14

让我们做一些成本/效益分析,好吗?

将“日期”字段保留为 VARCHAR2

优点:

  • 在报表上设置日期格式时计算成本为零(只要它始终以 MM-DD-YYYY 格式显示)。

缺点:

  • 无法建立索引(嗯,按时间顺序排列)
  • 由于 TO_DATE 调用,搜索计算量增加
  • 如果日期必须以不同的形式出现,则需要额外的计算/em> 某些报告上的格式(例如: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:

  • Zero computational cost when formatting the date on a report (as long it always appears in the MM-DD-YYYY format).

Cons:

  • Cannot be indexed (well, in the chronological sense)
  • Searching computation is increased because of TO_DATE calls
  • Requires extra computation if the date must appear in a different format on some report (for example: TO_CHAR(TO_DATE(date_column, 'MM-DD-YYYY'), 'DD-MON-YYYY'))
  • The column does not require all values to adhere to a particular date format, increasing the risk of a failure if TO_DATE is necessary.

Change the "date" field to DATE

Pros:

  • Can be searched upon without the overhead of TO_DATE
  • Can be indexed
  • "Bad" values cannot be inserted (although the dates may still make no sense from a business perspective)
  • Can be easily formatted

Cons:

  • I can't think of anything off the top of my head

Hopefully this will help in your particular decision from a technical perspective. There is always the business (or, office politics) perspective:

date dimension is not necessary

I will fix that for you:

date dimension is not necessary yet

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 to DATE. 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?

Oo萌小芽oO 2024-12-02 09:26:14

这里的“足够”是什么意思?我可以看到不使用 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?

没有你我更好 2024-12-02 09:26:14

应避免使用 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文