Best Way to generate Primary Keys in Java, Oracle RAC web application (Sequences?)
Environment: Oracle 11G, Web Portal Application development, Java, DataGuard available if needed
What is the best/recommended/proven way to generate integer surrogate keys for App development with Oracle as back-end in multi node RAC environment. There may be relatively frequent node switch-over.
Q. IS CACHED, non-ordered sequence is best preferred way in above environment?
data maintenance and quality,
Data ETL/Refreshing between environments,
trackable sequential PK IDs
Cache size (100?)
I don't really prefer guid. Should we use table based or app-generated IDs but then performance and seed data generated outside app can be an issue.
A. Yes, CACHED, non-ordered sequence is best preferred way, but unfortunately there may be gaps in cached sequences. Cache size of 100 may be too large, from my experience perhaps 10 or 20 is enough.
Originally Posted by daljitsb
The above is acceptable practice, but better check thus article:
Conceptual Muddling and Database Kludges
Last edited by LKBrwn_DBA; 08-13-2014 at 11:24 AM.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
So how does CACHED, non-ordered sequence behave, here is the scenario,
Originally Posted by LKBrwn_DBA
Cache size =10
Node 1 Active - Records created 1,2,3 (3 records total in table)
Node Changes - 2 is Active - Records created 11,12,13, 14 (7 records total in table)
Node Changes - 1 is Active - Records created 21, 22 (or are PKs going to be 4, 5) (9 records total in table)
Node Changes - 2 is Active - No new record added (9 records total in table)
Node Changes - 1 is Active - Records created - Is it going to be 23, 24 OR or is it going to be 6,7 OR or is it going to be 31, 32 (9 records total in table)
Also, What happens to PK numbers at:
* mirroring whole schema or database to another separate db using storage mirroring
* mirroring whole schema or database to another separate db using datapump
Click Here to Expand Forum to Full Width