Best Way to generate Primary Keys in Java, Oracle RAC web application (Sequences?)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Best Way to generate Primary Keys in Java, Oracle RAC web application (Sequences?)

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Lightbulb 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?

    Factors:
    security
    performance,
    data maintenance and quality,
    Data ETL/Refreshing between environments,
    failover
    trackable sequential PK IDs
    Data Auditing
    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.

    Please advice.

    -D

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    Quote Originally Posted by daljitsb View Post
    Environment: Oracle 11G, . . . E t c

    Q. IS CACHED, non-ordered sequence is best preferred way in above environment?

    Factors:
    security
    performance,
    data maintenance and quality,
    Data ETL/Refreshing between environments,
    failover
    trackable sequential PK IDs
    Data Auditing
    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.

    Please advice.

    -D
    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.

    The above is acceptable practice, but better check thus article:

    Conceptual Muddling and Database Kludges
    Last edited by LKBrwn_DBA; 08-13-2014 at 12:24 PM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    136
    Quote Originally Posted by LKBrwn_DBA View Post
    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.

    The above is acceptable practice, but better check thus article:

    Conceptual Muddling and Database Kludges
    So how does CACHED, non-ordered sequence behave, here is the scenario,
    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:
    * shutdown
    * hotbackup
    * mirroring whole schema or database to another separate db using storage mirroring
    * mirroring whole schema or database to another separate db using datapump

    -D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width