Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2005
    Posts
    13

    Unanswered: Common sequence?

    Hi

    In my schema I have something like 100 LOV tables (each LOV-table has a one-field PK).
    I want to define sequence/s for these PKs, and I have 2 options:
    1. Sequence for each PK - the standard way, I guess.
    2. One common sequence for all PKs.

    I find the second option very convenient, but I'm not sure whether it is OK to implement this way and I'll be happy to get some help

    You can assume that new records for all LOV-tables are not inserted often.

    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd rather see one sequence for all tables; the main purpose is to have a unique value for an ID, and a sequence will provide this. At the moment, I don't se a valid reason to create hundred (or more) sequences and attach every of them with "its own" table.

    There's a schema in our production database with ~1000 tables, and all primary key columns are populated using only one sequence. It works just fine (or, if not, nobody complained to me).

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Yep I dont see a problem as long as you arent using these sequences heavily i.e. you dont have lots of concurrent transactions using the sequence, otherwise it can become a bottleneck.

    Alan

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    We use one sequence rather than many... in our experience, we found it was confusing to developers as to which sequence to use when writing code that adds records. Of course, this could be easily compensated for by writing insert triggers for a table that use the appropriate sequence and avoid having the developer choose.

    So, multiple sequences are certainly usable, but takes careful management, whereas a single sequence is easier to manage. I suppose a conclusion I would draw is that it depends...

    My recommendation is single sequence as well.
    JoeB
    save disk space, use smaller fonts

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down


    I have something like 100 LOV tables (each LOV-table has a one-field PK).
    If every LOV table has a PK, why define sequences?

    I recommend NO sequences, what is the purpose?


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Remark removed.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I dont understand the requirement also if there's a key already in the table.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think he means that the PK is a synthetic one created from the sequence i.e. a table with (id, value) where id is generated by the sequence.

    Alan

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by AlanP
    Yep I dont see a problem as long as you arent using these sequences heavily i.e. you dont have lots of concurrent transactions using the sequence, otherwise it can become a bottleneck.
    If those are "real" LOV tables, then I'd assume that there won't be any transactions at all once the reference data has been created...

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, perhaps "no transactions at all" is a little bit too restrictive; now and then there IS a need to add a few records (but this fact shouldn't affect performance).

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Of course, the usual maintenance stuff to adjust the LOVs to the application. I was more referring to "concurrent" transactions which might not take place during application maintenance and would be the potential bottleneck of the single sequence solution.

  12. #12
    Join Date
    Oct 2006
    Location
    Romania
    Posts
    8
    We have 185 tables in a schema, each with it's own sequence.
    Using one sequence for all tables gives you two things - a small delay ( significant when you have hundreds of simultaneous transactions) and sequence - skipping ( not only do your PK id's are miles apart because the sequence is used for all tables, but if two transactions request a sequence simultaneously you will lose some id's because the sequence reserves some for each transaction), getting a 10.000.000 id only for 4.000.000 records.

    I found that the best way of using one sequence per table and not confusing them is just naming the sequences <table_name>_SEQ ( prefixing the table name to the sequence name).

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, but WHY would anyone want to have consecutive primary key values? It shouldn't matter whether there are "1, 2, 3, ..." or "1241241, 982928742, 2412424, ...", as long as they are unique. Some other columns should, perhaps, be used to help "humans" identify them, but - froim database's point of view - it really doesn't matter.

    Moreover, even if you use 1 table : 1 sequence, there's no guarantee that there won't be gaps in a sequence.

  14. #14
    Join Date
    Oct 2006
    Location
    Romania
    Posts
    8
    Yes, that is true, but I don't really like having extremely large sequence numbers.
    The maximum sequence that can be generated is 1E+27, one octillion, and although the database can hold the number, if you use an integer to hold the id in your application (just for the purpose of discussion) which is +2^31-1 in size, you have a problem, that's why I like to keep them as small as possible.

    The big issue here is not that I didn't like it, it's that after a few million records and simultaneous transactions you get performance issues.

    Read this asktom article

Posting Permissions

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