将日期时间数据存储为数字 [英] Store date-time data as numbers

问题描述

我正在开发的当前项目使用Oracle DBMS存储数据。在开发期间，我发现日期信息不存储在日期字段，但在一些VARCHAR2列有一些奇怪的格式。例如，查看此表：

The current project I’m working on uses an Oracle DBMS to store data. During development I found that Date information is not stored in a Date field, but in a VARCHAR2 column with some weird formatting. For example, look at this table:

CREATE TABLE "A_TABLE"
  (    
    "OSERC_FEC_INICIO_OS"            VARCHAR2(14 BYTE),
    "OSERC_FEC_FIN_OS"               VARCHAR2(14 BYTE),
    "OSERC_FEC_REGISTRO_PETICION"    VARCHAR2(14 BYTE),
    "OSERC_FEC_APROBACION_PETICION"  VARCHAR2(14 BYTE),
    "OSERC_FEC_LIQUIDACION_OS"       VARCHAR2(14 BYTE),
    "OSERC_FEC_EJECUCION_OS"         VARCHAR2(14 BYTE),
)

$ b b

字段 OSERC_FEC_REGISTRO_PETICION，OSERC_FEC_APROBACION_PETICION，OSERC_FEC_LIQUIDACION_OS 和 OSERC_FEC_EJECUCION_OS 存储日期信息，但被声明为VARCHAR2列。如果您检查数据，您会看到他们使用格式 YYYYMMDDHHMMSS 来存储该信息。

The fields OSERC_FEC_REGISTRO_PETICION, OSERC_FEC_APROBACION_PETICION, OSERC_FEC_LIQUIDACION_OS and OSERC_FEC_EJECUCION_OS stores date information but are declared as VARCHAR2 columns. If you check the data, you’ll see that they use the format YYYYMMDDHHMMSS to store that information.

I因为我需要构建在WHERE子句中使用这个日期的查询，我不知道该方法的索引性能将是什么。那么，我提到的设计中涉及的问题是什么？最好的日期字段是NUMBER而不是VARCHAR2？

I’m concerned because I need to build queries that uses this dates in the WHERE clause, and I’m not sure what will be the index performance with that approach. So, what are the problems involved in the design I mentioned? It would be better is the date fields where NUMBER instead of VARCHAR2?

推荐答案

如果日期存储为日期。将它们存储为数字而不是字符串引入了一组不同的问题。

It would be much better if the dates were stored as dates. Storing them as numbers rather than strings introduces a different set of problems.

如果你坚持使用存储为字符串的日期，为了允许列上的索引使用，您需要将您使用的日期作为参数以适当格式的字符串进行转换，然后依赖于以特定格式对字符串进行排序与​​实际日期的预期排序顺序相匹配这一事实。如果你比较字符串到日期或一个数字，你将得到隐式数据类型转换，这最多会导致性能问题，因为索引不能使用，最坏的情况下会产生不正确的结果或错误。

If you are absolutely stuck with dates stored as strings, in order to allow indexes on the columns to be used, you'd need to convert the dates you're using as parameters as strings in the appropriate format and then rely on the fact that sorting of strings in that particular format matches the expected sort order of actual dates. If you ever compare the string to date or to a number, you're going to get implicit data type conversion which, at best, will lead to performance problems because indexes cannot be used and at worst will generate incorrect results or errors.

假设避免数据类型转换，性能问题可能是由于以下事实：优化器在使用错误的数据类型时估计基数有很大困难。例如，Oracle知道在1/1/2012和1/1/2013之间有365天（或8760小时或525600分钟）。另一方面，在'20120101000000'和'20130101000000'之间有可能的字符串数十亿。这可能导致优化程序不使用索引，当你想它（或反之亦然），使用错误的类型的连接等。

Assuming you avoid data type conversion, the performance issues are likely to arise from the fact that the optimizer has a great deal of difficulty estimating cardinality when you use the wrong data type. Oracle knows, for example, that there are 365 days (or 8760 hours or 525600 minutes) between 1/1/2012 and 1/1/2013. On the other hand, there are billions of possible strings between '20120101000000' and '20130101000000'. That can cause the optimizer not to use an index when you would like it to (or vice versa), to use the wrong sort of join, etc.

这篇关于将日期时间数据存储为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！