does Unique constraint on multiple columns has performance issues -Oracle

2
votes

I am using Oracle database and I have a table for customers records and want to put a Unique key constraint on multiple varchar2 columns. like

CUST_ID (Number),

CUST_Name(varchar2),

Cust_N.I.C_NO(varchar2) will make a unique key.

when inserting new record through forms 6i, if ORA-00001 error comes, user will be informed that it was a DUPLICATED record.

Please advise me if there will be any database performance issue when records in this table will exceed 50000 or more.

If this is not a good practice to avoid inserting duplicate records, then please suggest any other approach.

regards.

1
performanceoracledatabase-designora-00001
You list three columns and say they "will make a unique key". Do you mean three unique keys? Because they seem odd candidates for a single compound key. - APC
This is absolutely the best way to avoid inserting duplicate records. - WW.

1 Answers

9
votes

Unique constraints are enforced though an index. So there are additional reads involved in the enforcement process. However, the performance impact of the constraint is minimal compared to the performance impact incurred by resolving duplicate keys in the database. Not to mention the business impact of such data corruption.

Besides, 50000 rows is a toy-sized table. Seriously, you won't be able to measure the difference of an insert with and without the constraints.