Skip to Main Content
  • Questions
  • Using datatype TIMESTAMP as Primary Key

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Etbin.

Asked: June 11, 2007 - 6:35 pm UTC

Last updated: June 17, 2011 - 10:56 am UTC

Version: 10.0.2

Viewed 10K+ times! This question is

You Asked

What is Your opinion about using datatype TIMESTAMP as the Primary Key of database tables?

It works well for me:

a) I don't have to use a sequence (one object less).

b) I don't have to set up another (in my case required) column "DATE_CREATED".

c) Space savings 11 bytes against 16 - our standard for sequence generated primary keys or even 20 - our standard changes recording sequence

But there are doubts also:

a) The distribution of column values is not uniform (as that produced by the sequence) any more, so the key should not hash well. I have no access to tkprof so is somehow useless for me to set up benchmarks.

b) The use of recursive insert (packaged) procedure (all tests went well - simultaneous insertion from three sessions using 1..100000 loops produced 300000 rows -> no missing rows). Here is the template:

procedure do_insert(p_a varchar2,p_n number,p_d date,the_count in out nocopy number) is
begin
the_count := the_count + 1;
insert into t (the_key,a,n,d)
values (timestamp(9),p_a,p_n,p_d);
commit;
exception
when dup_val_on_index then
if count < 1000 then
do_insert(column_values_in,the_count);
else
raise_application_error(-20000,'Retry count exceeded');
end if;
end;

Can You comment on what was said above. Do You see any other drawbacks? My proposal was rejected with the argumentation that TOAD and some in house made tools cannot handle timestamps.

Thanks and wish You long life (despite my almost daily visits to he site it is the first time in four years to catch the opportunity to submit a question)

and Tom said...

a) so what about "one object less" - that is not relevant to me in the grand scheme of things.

b) see a)

c) sequences are numbers, a varying length string from 0 to 22 bytes. Depends on the number of significant digits, highly unlikely to take 11 bytes

ops$tkyte%ORA10GR2> select vsize(1234567890123456789) from dual;

VSIZE(1234567890123456789)
--------------------------
                        11


a2) that doesn't make sense. both timestamps and sequences are monotonically increasing values. They are both going to be unique in your case (primary key) which means they hash pretty much perfectly.

you do not need tkprof to "benchmark" and you do have access to it (for everyone has access to Oracle XE, just download, install it, play with it)

b2) I won't reject it for the reason that some antiquated, obsolete tools cannot handle a datatype

I'll reject it because it can and will generate duplicates at some point (annoying) and the date_created - if you need it - is an attribute of the data, not the 'key' to the data.

I'd vote for using a true surrogate (which is what you are doing here) - a sequence.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

not even an option

Gabe, June 12, 2007 - 2:29 pm UTC

Tables can have other unique constraints/indexes as well ... so, handling on dup_val_on_index would have to get a bit more complicated in order to differentiate between timestamp collisions and true unique violations.

Also, multi-rows insert might be a problem ...

flip@FLOP> create table t1 ( ts timestamp primary key);

Table created.

flip@FLOP > create table t2 ( sq number primary key);

Table created.

flip@FLOP > create sequence s start with 1;

Sequence created.

flip@FLOP > insert into t2 select s.nextval from user_objects;

479 rows created.

flip@FLOP > insert into t1 select systimestamp from user_objects;
insert into t1 select systimestamp from user_objects
*
ERROR at line 1:
ORA-00001: unique constraint (FLIP.SYS_C0060259) violated

There is no benefit whatsoever.

I agree (100%)

Etbin Bras, June 13, 2007 - 4:23 am UTC

Date being a "sweeper", have You a scenario for "I'll reject it because it can and will generate duplicates at some point" at hand ?

It seems the environment I'm in successfully drived me off the "state of art" thinking.
This is an additional confirmation of my doubts about our "standards" following generally accepted practices as looking at Your site from my workstation is like looking in another universe.
It will not be professional (wise) and this is not the place to describe weird solutions I have to deal with but for the moment I can (must) live with it.
According to our National Statistical Office data at the present the frequecy of my name is 28 and instr(:EMPLOYER_NAME,chr(p)||chr(q)||chr(r)||chr(s)) where p+q+r+s=284; q=p-1; r=s+3; p*s=4950 is (not beeing unimployed) obviously > 0 and already being considered the Grumpy of the seven dwarfs is more than enough.
Nevertheless I feel my professional duty to be to point out any deviation from what is generally considered good practice, (Your site is my primary source) superiors like it or not - they almost always don't. I cannot help it, I must be able to sleep at night. (;-|)

It's been a privilege interacting with You and best wishes for the future.
Tom Kyte
June 13, 2007 - 8:13 am UTC

scenario:

you and i hit enter at the same time on a multi-cpu machine. We generate the same date.


CLARIFICATION

Etbin Bras, June 13, 2007 - 7:08 am UTC

Thanks to Gabe for for his contrbution and I apologize to the audience for making the question much too generic than I intended to. This is the first thing crossing my mind after seeing the followups. Just being too anxious to submit the question. It was the first time I saw the button after all.
I was seeking an opinion about using a TIMESTAMP as Primary Key for tables to hold data from some documents entered by users or generated by some procedures which is by nature of things a process occurring in one-by-one fashion and in our case definetly not at a high pace without expecting a substantial increase in next decade.
a) I can understand the "grand scheme of things" but unfortunatly in my situation I can't even suspect about it's existence. It is more the way Tom says: "why writing code if you don't have to".
b) The date_entered could be figured out from the timestamp (hopefully querying the index instead of the table)
c) The timestamp provides a compact ID
we store sequence numbers as varchar2 i.e. '0000000123456789' - the "standards" must be followed. So we could have around 16/11 or 20/11 more entries per PK index block ... we could have even more entries stored if we just let numbers to be numbers.
I am aware that this can not bring major benefits in all situations, but in some of them, it could, as we say, "kill more flies with one blow".
Tom Kyte
June 13, 2007 - 8:26 am UTC

comment on C

I fell out of my chair, that hurt. we store numbers in a string, ouch.


I would not use a timestamp as a primary key, no.
I would however beg someone to look at the standard operating procedures in place and ask "WHY"

CONCLUSIONS

Etbin Bras, June 13, 2007 - 11:56 am UTC

I would not use a timestamp as a primary key, no.

Neither will I. Your multi-cpu scenario is more than convincing. It is a very valid argument and I'm certain I will not be the only one to consider it from now on. My general attitude is to avoid potential problems, not to create them in the first place and be proud of having solved them after that. So no remarks about my insert procedure being able to handle it etc. etc. etc.. Period.

I would however beg someone to look at the standard operating procedures in place and ask "WHY"

Already did, many times. Thinking back it is very probable that this timestamp idea emerged from this situation. We store numbers as strings, (don't fall off your chair again) we used to store dates as strings (we made some progress, didn't we), so why not using a timestamp as a primary key (at least we will save some space and gain some speed). There isn't much that can be done. Unfortunately the someones that can be begged to have a look are the authors and proud custodians of "standards" that make you fall off chairs. Off record they are able to admit that the situation is getting worse but officially they take it very personally, the existing system must be defended at all cost.

p.s. if You intend to set up some kind of Hall of Oracle Horrors I will not be short of contributions for sure.



Index on Timestamp column

A reader, June 08, 2011 - 4:16 am UTC

Hi Tom

Problem Description :
(1) A table with a monthly range partition based on the created_date column ,with each partition holding nearly a million of records.
(2)Reports on the screens will be using this table based on the date range supplied along with certain other filter criteria.

Do you suggest creating local partitioned index on the created_date column of the table to be referred in the query plan.
Looking forward to your suggestion. Thanks .
Tom Kyte
June 08, 2011 - 10:45 am UTC

you say timestamp in the subject.

you use the word date everywhere else.

confusing.....




Do you suggest creating local partitioned index on the created_date column of
the table to be referred in the query plan.


it depends. Would that index be useful to many queries that would retrieve a few hundred or low thousands of records? That is, is the created_date column selective enough to be indexed. That the table is partitioned or not is not really relevant - it is all about "how many rows will this retrieve out of how many".

You'll have a million rows in there. Will this proposed index by used to get

a) very few rows (hundreds)
b) a lot of rows (thousands - how many thousands?)
c) a ton of rows


If (a), yes, probably index
If (b), maybe, maybe index
If (c), nope, no way - full scan (or use some other part of the where clause to index by)

A reader, June 13, 2011 - 1:24 am UTC

Thanks Tom.
Lets assume that the column is a timestamp column , does that alter your answers in that case.

Also the selectivity of the where clause can vary as the timestamp range is to be selected from the UI of the Application.



Tom Kyte
June 17, 2011 - 10:56 am UTC

no, no change to the answer.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library