2

Caveats:

  • Let me first clarify that this is not a question about whether to use surrogates primary keys or not.

  • Also, this is NOT a related to identities (SQL Server) / Sequences (Oracle) and their pros / cons. I did get a fair bit of idea about that thanks to this, this and this

    Question:

    I come from a SQL Server background and have always used identity columns as surrogate primary keys for most tables.

    Based on my knowledge of Oracle, I find that the nearest equivalent in Oracle are SEQUENCES which can be used to simulate something similar to Identity in SQL server.

    As I am new to Oracle and my database has 100+ tables, the main thing that i am concerned about :-

  • Considering i have to create a sequence for each table in Oracle (almost), would this be the standard accepted implementation for simulating Identity or is there a better / easier way to achieve this kind of implementation in Oracle?
  • Are there any specific GOTCHA's related to having so many sequences in Oracle?

    The system supports both Oracle 10G and 11G

    • Community
    • 1
    • 1
    Jagmag
    • 10,283
    • 1
    • 34
    • 58

    2 Answers2

    2

    Considering i have to create a sequence for each table in Oracle (almost), would this be the standard accepted implementation for simulating Identity or is there a better / easier way to achieve this kind of implementation in Oracle?

    Yes, it is very typical in Oracle to create a sequence for each table. It is possible to use the same sequence for several tables, but you run the risk of making it a bottleneck by using a single sequence for many/all tables: see this AskTom q/a

    Are there any specific GOTCHA's related to having so many sequences in Oracle?

    None that I can think of.

    Tony Andrews
    • 129,880
    • 21
    • 220
    • 259
    2

    100+ tables is not very many. I routinely work on databases with several hundred sequences, one for each table. The more the merrier.

    It's even conceivable to have more sequences than tables - unlike identity columns in other DBMSs, sequences can be used for more than just creating surrogate key values.

    An alternative is to use GUIDs - in Oracle you can call SYS_GUID to generate unique values.

    A good article, followed by comments with pros and cons for both approaches: http://rwijk.blogspot.com/2009/12/sysguid.html

    Jeffrey Kemp
    • 59,135
    • 14
    • 106
    • 158
    • The advice is reassuring as it confirms that i am not doing something silly due to my inexperience with the product. Appreciate it. +1. Thanks!! – Jagmag Sep 25 '10 at 03:49