Oracle DB, creating unique constraint on multiple columns for insert , how about performance

Question

Friends,

Am new to DB venture, I needed some help/information. There is a table in our project say "record_table" , values in it is inserted using C++ code.

This table has multiple columns, out of which three columns say for eg. "serialNo, type, sub_type" that C++ code is inserting duplicate values for combination of these columns( these columns are no where unique or primary for that table). But the combination of 3 columns should be unique.

Now we want to make sure duplicates for this combination shouldnt be inserted. I was thinking of adding unique constraint for these columns so that when new record is to be inserted with these duplicated values it will not allow to .

I assume this should work, but I have a doubt will it hit the performance, the C++ binary runs daily and it inserts around 2 million records. Will creating unique constraint hit performance.(Mean will the run time slow down or since the table has millions of records will creating unique constraint make no sense as it has to make a hash of these columns etc)

Please suggest if you can.

Answer 1

Unique constraints are enforced through an index. Chances are you need that index anyway, for querying the data back again, so the overhead of maintaining it is irrelevant.

The real question is, what is the performance impact of handling duplicate records if you don't enforce the constraint? Generally speaking the performance impact of enforcing constraints is trivial compared to fixing data corruption.