Unique Constraint performance

1
votes

When having a table with 450 million records and a unique constraint (no primary key, just constraint made out of 6 columns), how can I improve its performance while inserting 5 million rows daily.

at the moment I just disable the constraint and enable it after the loading has finished. But this takes some time.

By the way, there is no unique index supporting the constraint... it will just get super huge

1
oracleoracle11goracle10gdata-warehouseunique-constraint
"By the way, there is no unique index supporting the constraint..." - what do you mean by that? There has to be an index behind the constraint. Or you mean that you have non-unique index behind that constraint? - Yaroslav Shabalin

1 Answers

3
votes

In case your import procedure ensures uniquenes of new rows you can enable the constraint with NOVALIDATE, then existing data in the table are not checked.

See here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm#SQLRF52204