23 June, 2012

Max Function vs Databse Sequence

In database design we always use sequence numbers for primary keys(unique values) and We can implement this by database sequences or getting max value in the column using MAX function.
I called this approach "Sequence VS MAX()".
I will explain every approach first then and list drawbacks for every one.


1- Max value in the column
We get maximum value in the unique column and add one value to it
For example
 SELECT NVL (MAX (COLUMN_NAME) + 1, 1)   
 INTO :MAX_VALUE   
 FROM TABLE_NAME;  

Drawbacks of this approach
a- No Concurrency Control
If two sessions run at the same time .
first session insert records but doesn't commit.
second session insert some records and commit, then it will get invalid unique identifiers which first session has reserved it but it doesn't commit.
If first session try to commit after second session commit, redundant unique identifiers are existed.

b-Bad Performance
Because of Max() function, the query will do full table scan to get max value.
If I created index on column used to get maximum value, it will enhance performance.

2-Database Sequence
Create database sequence object and get next value as maximum value for the column.
For example
 CREATE SEQUENCE MY_SEQ  
   START WITH 1  
   MAXVALUE 999999999999999999999999999  
   MINVALUE 1;  
   
 SELECT MY_SEQ.NEXTVAL   
 INTO :MAX_VALUE   
 FROM DUAL;  

Drawbacks of this approach
a- Gaps in unique identifiers because of losing some values in sequence according to delete operation.

Conclusion 
use database sequence for unique identifiers as it has less drawbacks and its drawbacks are bug less while Max function has "No concurrency control" which is bug in your code.

Some developers use global package variable for handling unique identifiers too.

Thanks
Mahmoud A. El-Sayed
Recommended Post Slide Out For Blogger