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.
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".
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 .
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.
June 17, 2011 - 10:56 am UTC
no, no change to the answer.