Re: does unique constraints hurt insert performance

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 13 Aug 2006 10:08:17 -0700
Message-ID: <1155488897.347763.56890@b28g2000cwb.googlegroups.com>

sunh11373 wrote:
> Hi,
>
> Unfortunately in my current dev environment, I can only have about 50M
> rows. We are still in design phase. I just want to see whether using
> "unique constraint" to keep data integrity for large database table
> with high insert rate is a good design idea or not .
And what are alternatives?
Checking that in application level? Have you thought a reliable mechanism to do that in app level? And have you measured the overhead of doing that?

>Our initial test
> with 30M does not show any problem. But would like to see anyone have a
> larger database ever experienced some performance problem using this
> approach.
>
> "Big" means more than 100% performance hit, e.g. from 10ms (w/o unique
> constraint) -> > 20ms (w/ unique constraint).
>
> Duplicate data is not allowed. If dected, the insert should fail. If
> the "unique constrain" approach does not work, then the caller has to
> ensure the uniqueness before calling the database.
Again questions - using what? Select to DB? Consider the time it'll take and the possibility that you check and someone inserts the same value between your check and your insert with commit.

I'd say that there simply isn't any reliable alternative that will do that faster.

BTW it is worth to ensure that most of the time your insert suceeds, because the rollbacks that Oracle had to make really can hurt performance. Below you can see that insert of 100000 rows where all rows succeed takes ~10 secs. Insert of the same number of rows where previous rows weren't commited took ~44 secs. Insert of the same number of rows where previous rows were commited took 4min and 33 secs. And you can see how statistics for rollback changes - undo records applied and transaction rollbacks were grown for each insert pack.

Gints Plivna
http://www.gplivna.eu

SQL> create table uk_check(i number);

Table created.

Elapsed: 00:00:00.00
SQL> alter table uk_check add constraint ukc_uk unique(i);

Table altered.

Elapsed: 00:00:00.00
SQL> select name, value from v$mystat m, v$statname s 2 where m.statistic# = s.statistic#
3 and upper(name) like '%ROLLBACK%' 4 /

NAME
VALUE

user rollbacks
  0
transaction tables consistent read rollbacks   0
rollbacks only - consistent read gets
19
cleanouts and rollbacks - consistent read gets   0
rollback changes - undo records applied
  0
transaction rollbacks
  0

6 rows selected.

Elapsed: 00:00:00.00
SQL> declare
2 begin
3 for i in 1..100000 loop

  4      begin
  5        insert into uk_check values (i);
  6      exception when dup_val_on_index then null;
  7      end;

8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.04
SQL> select name, value from v$mystat m, v$statname s 2 where m.statistic# = s.statistic#
3 and upper(name) like '%ROLLBACK%' 4 /

NAME
VALUE

user rollbacks
  0
transaction tables consistent read rollbacks   0
rollbacks only - consistent read gets
50
cleanouts and rollbacks - consistent read gets   0
rollback changes - undo records applied
  0
transaction rollbacks
  0

6 rows selected.

Elapsed: 00:00:00.00
SQL> declare
2 begin
3 for i in 1..100000 loop

  4      begin
  5        insert into uk_check values (i);
  6      exception when dup_val_on_index then null;
  7      end;

8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.04
SQL> select name, value from v$mystat m, v$statname s 2 where m.statistic# = s.statistic#
3 and upper(name) like '%ROLLBACK%' 4 /

NAME
VALUE

6 rows selected.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> declare
2 begin
3 for i in 1..100000 loop

  4      begin
  5        insert into uk_check values (i);
  6      exception when dup_val_on_index then null;
  7      end;

8 end loop;
9 end;
10 /

PL/SQL procedure successfully completed.

Elapsed: 00:04:33.06
SQL> select name, value from v$mystat m, v$statname s 2 where m.statistic# = s.statistic#
3 and upper(name) like '%ROLLBACK%' 4 /

NAME
VALUE

6 rows selected.

Elapsed: 00:00:00.00

Gints Plivna
http://www.gplivna.eu Received on Sun Aug 13 2006 - 12:08:17 CDT