Hi,
I have been experimenting with a date column in a primary key, or actually I tried using a timestamp with time zone in a primary key.
While researching whether there was a way to avoid ORA-02329, I found the following:
K15> create table dumdum
2 (datum date not null
3 ,naamp varchar2( 30 ) not null);
Table created.
K15>
K15> alter table dumdum
2 add constraint d_pk
3 primary key
4 (datum, naamp)
5 using index;
Table altered.
K15>
K15> select ind.index_type
2 from user_indexes ind
3 where ind.index_name = 'D_PK';
INDEX_TYPE
---------------------------
NORMAL
1 row selected.
K15>
K15> insert into dumdum
2 (datum
3 ,naamp )
4 select sysdate - (level/1440)
5 , 'nomen nescio'
6 from dual
7 connect by level < 1000
8 ;
999 rows created.
K15>
K15> analyze index d_pk validate structure;
Index analyzed.
K15> analyze table dumdum compute statistics;
Table analyzed.
K15>
K15> select naamp
2 from dumdum
3 where datum > to_date('16-06-2011 15.46.16', 'dd-mm-yyyy hh24.mi.ss' )
4
K15>
For the last select statement I get the following "explain plan":
SELECT STATEMENT CHOOSE
Cost: 2 Bytes: 247 Cardinality: 13
1 INDEX RANGE SCAN UNIQUE D_PK
Cost: 3 Bytes: 247 Cardinality: 13
This behavior lived up to my expectations.
Then, I tried this:
K15> create table dumdum
2 (datum date not null
3 ,naamp varchar2( 30 ) not null);
Table created.
K15>
K15> alter table dumdum
2 add constraint d_pk
3 primary key
4 (datum, naamp)
5 using index;
Table altered.
K15>
K15> alter table dumdum
2 modify datum timestamp(6) with time zone;
Table altered.
K15>
K15> select ind.index_type
2 from user_indexes ind
3 where ind.index_name = 'D_PK';
INDEX_TYPE
---------------------------
NORMAL
1 row selected.
K15>
K15> insert into dumdum
2 (datum
3 ,naamp )
4 select sysdate - (level/1440)
5 , 'nomen nescio'
6 from dual
7 connect by level < 1000
8 ;
999 rows created.
K15>
K15> analyze index d_pk validate structure;
Index analyzed.
K15> analyze table dumdum compute statistics;
Table analyzed.
K15>
K15> select naamp
2 from dumdum
3 where datum > to_date('16-06-2011 15.46.16', 'dd-mm-yyyy hh24.mi.ss' )
4
K15>
So, at first glance, the alter table statement to change the datatype from DATE to TIMESTAMP seems like a way of fooling Oracle. But the explain plan reveals a different story:
SELECT STATEMENT CHOOSE
Cost: 4 Bytes: 1,25 Cardinality: 50
1 TABLE ACCESS FULL DUMDUM
Cost: 4 Bytes: 1,25 Cardinality: 50
I was only fooling myself. :-0
But I wasn't done with my research:
K15> create table dumdum
2 (datum timestamp(6) with time zone not null
3 ,naamp varchar2( 30 ) not null);
Table created.
K15>
K15> create unique index d_ind
2 on dumdum
3 (datum, naamp);
Index created.
K15>
K15>
K15> select ind.index_type
2 from user_indexes ind
3 where ind.index_name = 'D_IND';
INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
1 row selected.
K15>
K15> insert into dumdum
2 (datum
3 ,naamp )
4 select systimestamp - (level/1440)
5 , 'nomen nescio'
6 from dual
7 connect by level < 1000
8 ;
999 rows created.
K15>
K15> analyze index d_ind validate structure;
Index analyzed.
K15> analyze table dumdum compute statistics;
Table analyzed.
K15>
K15> select naamp
2 from dumdum
3 where datum > to_date('16-06-2011 15.56.16', 'dd-mm-yyyy hh24.mi.ss' )
4
K15>
Now, my explain plan looks fine:
SELECT STATEMENT CHOOSE
Cost: 2 Bytes: 1,25 Cardinality: 50
1 INDEX RANGE SCAN UNIQUE D_IND
Cost: 3 Bytes: 1,25 Cardinality: 50
Why is Oracle so adamant about not allowing a timestamp with time zone in a unique key? And, given their position on the matter, where does their tolerance for a unique index come from?
By the way, if I had a say in it, I would not allow anything that even remotely looks like a date to be part of a primary key, but that's another discussion.
Thanks,
Remco
P.S. All this is on Oracle9i Enterprise Edition Release 9.2.0.8.0. Is it different on 10g or 11g?