Using Sequences For Data Integrity

You can use as some keys in your tables. Especially these keys are unique and identifies the row of a table. Sometimes you take care of these keys. Incrementing the key values 1 by 1 is a useful practise. But how the incrementation must be?
There are some ways. One is creating a trigger and taking max value + 1 of table for new key value. This is not a good solution. Because of invalidates your data integrity. For multi user environments same values can be inserted to table. If you create a unique constraint on key column you can somehow handle duplicate values. In performance situation, it is not preferable.
To demonstrate this, i opened two sessions. i wrote a before trigger and insert 10000 records to table. As you see it will create duplicates.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table t;
Table dropped
Executed in 0,121 seconds
SQL> create table t(x number, y number);
Table created
Executed in 0,02 seconds
SQL> INSERT INTO t(x, y) values(1,1);
1 row inserted
Executed in 0 seconds
SQL> CREATE OR REPLACE TRIGGER trg_t
  2    BEFORE INSERT ON t
  3    FOR EACH ROW
  4  DECLARE
  5    i t.x%TYPE;
  6  BEGIN
  7    SELECT MAX(x) INTO i FROM t;
  8    :NEW.x := i + 1;
  9  END trg_t;
 10  /
Trigger created
Executed in 0,02 seconds
SQL> INSERT INTO t(y) values(1);
1 row inserted
Executed in 0,01 seconds
SQL> SELECT * FROM t;
         X          Y
---------- ----------
         1          1
         2          1
Executed in 0,02 seconds
SQL> commit;
Commit complete
Executed in 0 seconds


Now i opened two sessions concurently and runs the same query both sides.

SQL>
SQL> BEGIN
  2    FOR i IN 3 .. 10000 LOOP
  3      INSERT INTO t( y) values( i);
  4      COMMIT;
  5    END LOOP;
  6  END
  7  ;
  8  /
PL/SQL procedure successfully completed
Executed in 60,046 seconds


--This is other session
PL/SQL procedure successfully completed
Executed in 68,199 seconds

Now checking the duplicates:
SQL> SELECT count(count(*)) FROM t group by x having count(*) > 1;
COUNT(COUNT(*))
---------------
            470
Executed in 0,15 seconds

As you see there are some duplicates. You can avoid by defining unique constraint. But again this will make performance worse.


On the other side you can create a sequence and every insert you can take nextval from sequence. It consists your data integrity gains performance.
SQL> drop table t;
Table dropped
Executed in 0,07 seconds
SQL> create table t(x number, y number);
Table created
Executed in 0,01 seconds
SQL> drop sequence seq_t;
Sequence dropped
Executed in 0,33 seconds
SQL> create sequence seq_t start with 1 increment by 1;
Sequence created
Executed in 0,01 seconds


Now i opened two sessions concurently and runs the same query both sides.
SQL>
SQL> BEGIN
  2    FOR i IN 3 .. 10000 LOOP
  3      INSERT INTO t(x, y) values(seq_t.nextval, i);
  4      COMMIT;
  5    END LOOP;
  6  END;
  7 
  8  /
PL/SQL procedure successfully completed
Executed in 4,627 seconds
PL/SQL procedure successfully completed
Executed in 5,077 seconds

Now checking the duplicates:
SQL> SELECT count(count(*)) FROM t group by x having count(*) > 1;
COUNT(COUNT(*))
---------------
              0
Executed in 0,15 seconds



When using key values it is better to use sequence. It is atomic.