在 Oracle 中选择更高的时间戳精度有什么缺点？

What are the disadvantages of choosing higher timestamp precision in Oracle?

提问by Leonid

Oracle allows to specify precision of TIMESTAMPtype in a table - the number of digits in the fractional part of the SECONDdatetime field. Are there any disadvantages of specifying maximal precision TIMESTAMP(9)?

Oracle 允许TIMESTAMP在表中指定类型的精度-SECOND日期时间字段的小数部分中的位数。指定最大精度有什么缺点TIMESTAMP(9)吗？

One reason I could think is that this information may be used for prettier output by Oracle tools.

我认为的一个原因是该信息可用于 Oracle 工具的更漂亮的输出。

Maximum of 9 digits suggests that the field is stored as a 4 byte integer so it should not have any performance implications, please correct if I'm wrong here.

最多 9 位数字表明该字段存储为 4 字节整数，因此它不应该有任何性能影响，如果我在这里错了，请更正。

回答by Jon Heller

There are no disadvantages, use timestamp(9) if it makes sense.

没有缺点，如果有意义，请使用时间戳（9）。

Timestamp(9) and timestamp(1) use the same amount of space, and their performance is identical. I could only find one case where there was a performance difference, and in that case timestamp(9) was actually faster than timestamp(1).

Timestamp(9) 和 timestamp(1) 使用相同的空间量，它们的性能是相同的。我只能找到一种存在性能差异的情况，在这种情况下，timestamp(9) 实际上比 timestamp(1) 快。

(I'll spare you the many lines of boring code inserting into timestamp(1) and timestamp(9) columns and comparing different operations on them.)

（我将省去插入时间戳（1）和时间戳（9）列并比较对它们的不同操作的多行无聊代码。）

This demonstrates that they use the same amount of space (inserting many values and comparing dba_segments):

这表明它们使用相同的空间量（插入许多值并比较 dba_segments）：

--Create tables with timestamps and populate them with the same data (with different precision)
--Set initial and next to a low value so we can closely check the segment size)
create table timestamp1 (t1 timestamp(1), t2 timestamp(1), t3 timestamp(1), t4 timestamp(1), t5 timestamp(1))
storage(initial 65536 next 65536);

insert into timestamp1
select current_timestamp(1), current_timestamp(1), current_timestamp(1), current_timestamp(1), current_timestamp(1)
from dual connect by level <= 100000;

create table timestamp9 (t1 timestamp(9), t2 timestamp(9), t3 timestamp(9), t4 timestamp(9), t5 timestamp(9))
storage(initial 65536 next 65536);

insert into timestamp9
select current_timestamp(9), current_timestamp(9), current_timestamp(9), current_timestamp(9), current_timestamp(9)
from dual connect by level <= 100000;


--Segment size is identical
select segment_name, bytes from dba_segments where segment_name in ('TIMESTAMP1', 'TIMESTAMP9');

--SEGMENT_NAME   BYTES
--TIMESTAMP1     8388608
--TIMESTAMP9     8388608

This is where timestamp(9) is faster, when using current_timestamp, which you'll probably need to use at some point to generate the data. But we're only talking about the difference between about 0.175 and 0.25 seconds on my slow desktop to generate 100K timestamps. I'm not sure why timestamp(9) is faster, maybe timestamps are always generated as timestamp(9) and then rounded to other precisions?

这是时间戳（9）更快的地方，当使用 current_timestamp 时，您可能需要在某些时候使用它来生成数据。但我们只是在谈论在我的慢速桌面上大约 0.175 和 0.25 秒之间的差异，以生成 100K 时间戳。我不确定为什么时间戳（9）更快，也许时间戳总是作为时间戳（9）生成然后四舍五入到其他精度？

--current_timestamp(9) is slightly faster than current_timestamp(1)
select count(*) from
(
  select *
  from dual
  --where current_timestamp(9) = current_timestamp(9)
  where current_timestamp(1) = current_timestamp(1)
  connect by level <= 100000
);

EDIT: The performance difference exists in 10g but not 11g.

编辑：性能差异存在于 10g 而不是 11g。

回答by usr-local-ΕΨΗΕΛΩΝ

The problem is performance. You must trade it with precision. Smaller numbers are readen and written in fewer CPU instruction. A CPU instruction takes less than a nanosecond, but if your server serves millions of transactions you might find some performance decrease, and this suggests you to adopt less precision, or even no precision (round all timestamps to the seconds is quite acceptable in most scenario, even in banking).

问题是性能。你必须精确地交易它。使用较少的 CPU 指令读取和写入较小的数字。CPU 指令花费的时间不到一纳秒，但如果您的服务器处理数百万个事务，您可能会发现性能有所下降，这表明您采用较低的精度，甚至不采用精度（在大多数情况下，将所有时间戳舍入到秒是完全可以接受的） ，即使在银行业）。

But if you, for some reason, ie. real-time system logging, need more precision, you are forced to use a higher precision and thus get a performance decrease. If your server doesn't process a great number of tps you have almost no performance impact, but if you don't need precision you're wasting memory.

但是，如果您出于某种原因，即。实时系统日志，需要更高的精度，你被迫使用更高的精度，从而导致性能下降。如果您的服务器不处理大量的 tps，您几乎不会对性能产生影响，但如果您不需要精度，那么您就是在浪费内存。

Hope to have been of help. If you want to share with us your DB requirements, we might help you choose your best compromise.

希望有所帮助。如果您想与我们分享您的数据库要求，我们可能会帮助您选择最佳折衷方案。

回答by Greg Buehler

The difference is not in the technical use of the Timestamp data type, but the application. FERC and NERC often require a certain precision when used in applications labeled critical infrastructureand as such they will use the highest precision made available.

区别不在于 Timestamp 数据类型的技术使用，而在于应用程序。FERC 和 NERC 在标记的应用中使用时通常需要一定的精度critical infrastructure，因此它们将使用可用的最高精度。

Of course, making the suits happy with their sequence of events records often requires doing more than laid out by CIP-002 through CIP-009

当然，让西装对他们的事件记录感到满意通常需要做的不仅仅是 CIP-002 through CIP-009

回答by Hugo R

No disadvantages if you always going to use the data as "date/timestamp" datatype inside Oracle and in the middle tier, however you have to see how your entire application/solution is using that column.

如果您总是将数据用作 Oracle 内部和中间层中的“日期/时间戳”数据类型，则没有任何缺点，但是您必须查看整个应用程序/解决方案如何使用该列。

• Are you truncating the data before you display it ?
• Is it a requirement for compliance and it is mainly read ?
• Are you converting that column to a string to compare it to another column ?
• is it a requirement for auditing or for order capturing ?

• 您是否在显示之前截断数据？
• 它是合规性要求吗，主要是阅读？
• 您是否将该列转换为字符串以将其与另一列进行比较？
• 这是审计或订单捕获的要求吗？

Don't worry too much about the reads and writes performance differences, there are negligible, evaluate your overall requirements as a on whole from storage to UI.

不要太担心读写性能的差异，可以忽略不计，从存储到UI，从整体上评估你的整体需求。