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

4 comments:

  1. Of course, the gaps "drawback" due to deletes is also a drawback in your MAX() approach.
    You will be pounding the table, using the MAX() approach - also, it is inefficient in large tables.

    ReplyDelete
  2. Gaps in Sequences may occur because of caching mechanism on database restart. The pregenerated values may get lost when database is restarted. They may also be a gap when a user decide to rollback a transaction and has already fetched the sequence.

    Performance gain is observed as there is better concurrency control and optimized at its best by Oracle engine itself.

    Hope this helps!
    Piyush

    ReplyDelete
  3. this blog is filled junk http://casinoonlinex.wordpress.com online casino

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...