Hi Tom,

We have a table with a non incremental composite key of (dialaog_id, insertion_date).

This creates problems since we have insertions in the middle of the leaves. Data has to be relocated.

We cannot use incremental keys since this creates problems when importing/exporting data from/to different DBs. (We would have collisions)

Is it a good idea to use a clustered key (different from the composite primary key) on insertion_date?

This would create a non-unique clustered index on insertion_date. Some DBs support this by adding a uniquifier.

Questions:
1) Does Oracle support this?
2) Would this solve our non incremental issue? Would the new non unique clustered index on insertion_date be incremental?
3) Is it recommended? I read that it will perform well on date range queries.

What do you propose?

select * from t where dialaog_id = :id;

select * from t where dialaog_id = :id and insertion_date > :start_date

create table t (
  dialaog_id int, insertion_date date, ... other cols ...,
  primary key ( dialaog_id, insertion_date )
) organization index;