Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2009
    Posts
    25

    Unanswered: Oracle function/sql to find sequential numbers skipped

    I am inserting some records in a table which follow a 1,2,3 sequence. However sometimes the seq_num skips the numbers as below, 4,7,10 etc are skipped. How can I query to find these missing seq_nums? thanks!

    Code:
    UnitID	seq_num
    1230001	1
    1230002	2
    1230003	3
    1230004	5
    1230005	6
    1230006	8
    1230007	9
    1230008	11

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    There are quite a few ways to do it. The simplest may be:
    Code:
    SELECT rownum AS seq_num
    FROM all_tables
    WHERE rownum < 200 -- your max seq num
    
    MINUS
    
    SELECT seq_num FROM my_table
    The real question is why are you worried about it? If seen too many MySQL developers trying to abuse their surrogate keys not to be concerned... You're not a MySQL developer in the wrong forum are you? LOL.
    Last edited by artacus72; 04-15-10 at 13:43.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Something like:
    Code:
    SELECT last_expected
    FROM (
      SELECT unitID, 
             seq_num,
             seq_num - 1 as last_expected,
             lag(seq_num) over (order by seq_num) as last_seq
      FROM units
    ) t
    WHERE last_expected <> last_seq
    But this will only work if the gaps are exactly one apart. If more than one value is missing it will only show the highest.

  4. #4
    Join Date
    Feb 2009
    Posts
    25
    That worked perfectly! thank you so much.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Here is another solution that will output all missing seq_num values even if the gap is greater than one:
    Code:
    SELECT missing 
    FROM 
    (
        SELECT u.seq_num, st.seq_num as missing
          FROM  (SELECT rownum as seq_num FROM dual connect by rownum < (SELECT max(u2.seq_num) FROM units u2)) st 
          LEFT JOIN units u ON st.seq_num = u.seq_num
    ) t2
    WHERE t2.seq_num is null
    ORDER BY 1 asc
    ;
    That will probably be quite slow for large tables ...

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    my advice is do not rely on perfect sequential numbers and also to never worry about it.
    oracle needs to cache a certain amount of sequences ahead of time PER INSTANCE.
    whenever an instance goes down you lose that cache.

    The cache per instance is necessary to avoid overhead of generating sequence numbers on the fly.

    If you need the sequence number for counts I would recommend just COUNT(*).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    > If you need the sequence number for counts I would recommend just COUNT(*).

    With the standard caveat that if two users try to insert at the same time you'll have trouble.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by artacus72 View Post
    > If you need the sequence number for counts I would recommend just COUNT(*).

    With the standard caveat that if two users try to insert at the same time you'll have trouble.
    not sure what you mean.
    select count(*) from TABLENAME;

    this will give you the total number of rows on the table at that point in time regardless of anything.

    also, there is no "at the same time". One session always commits before another.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by The_Duck View Post
    select count(*) from TABLENAME;

    this will give you the total number of rows on the table at that point in time regardless of anything.
    At the point in time _when you started counting_, but not necessarily at the point in time when you ended counting.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >At the point in time _when you started counting_, but not necessarily at the point in time when you ended counting.
    Oracle provides a Read Consistent view of the DB data.
    Count will be accurate as of the time the operation starts.
    It does not matter what happens after the COUNT commences.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    >
    It does not matter what happens after the COUNT commences.
    I would argue that in the case that started this thread it actually matters.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    I still say you are wasting your time unless I am missing some integral fact that has not been explained yet. Who cares if you are missing numbers in a sequence? Considering most (99.9999&#37 of tables have deletes then you will always have gaps in a sequence.

    To spend time figuring out what sequences are missing requires a full table scan and a count(*) would accomplish the same.

    Other than just figuring out how many gaps you have for no purpose whatsoever I fail to see the need to count the gaps when you can simply count the rows much faster.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Other than just figuring out how many gaps you have

    SELECT MAX(SEQ)-COUNT(SEQ) MISSING FROM SEQ_TBL;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by anacedent View Post
    >Other than just figuring out how many gaps you have

    SELECT MAX(SEQ)-COUNT(SEQ) MISSING FROM SEQ_TBL;
    my point exactly. Thanks.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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