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

Question

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

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

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.

Answer 1

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

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).

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

This demonstrates that they use the same amount of space (inserting many values and comparing 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?

--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.