This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Sep 24, 2010 5:29 AM by 635471 Go to original post RSS
  • 45. Re: Sequence Issue
    731280 Explorer
    Currently Being Moderated
    I'm not insisting that 'gaps are OK'. Sometimes a requirement is really a requirement.

    But you have one of the following:
    <li>Gaps are OK
    <li>Gaps are OK but they are annoying or undesirable
    <li>Gaps are Not (with a capital N) OK , they will cause processes to break, cause us to fail an audit, etc.

    And, if they are truly Not OK, then a solution which causes fewer gaps than an Oracle sequence, but can still produce gaps nevertheless, is Not OK.

    If your gap-vulnerable solution is acceptable, then the requirement must really be of the annoying/undesirable type. This would be important to know, because it would allow you to explore other options and weigh them on their merits. A slow system can be annoying and undesirable, too - which can be a trade-off against the annoyance/undesirability of occaisional gaps.

    Edited by: mtefft on Sep 23, 2010 5:12 AM
  • 46. Re: Sequence Issue
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    David_Aldridge wrote:

    Sometimes there is a legal requirement that a sequence be gap free.
    In that case, the technical feature to use is not a sequence. It is a very specific and special attribute that needs careful consideration as how to implement it electronically.

    One approach would be to have the special attribute implemented as a unique constraint in a separate table that provides the "+link+" to the underlying row data - where the row data (different table) is uniquely identified by a surrogate key (as provided by an Oracle sequence).

    But treating this legally required attribute as the surrogate key itself for that entity seems to me to be ignorant of what a surrogate key is and how it is applied at a physical db level.

    For such a legal requirement, the system will be in legal violation if the "sequence" has gaps, or a "sequence" go missing and so on. This means auditing, additional security and integrity and validation rules. All these need to be considered. Yet in all the time I have seen this "gapless sequence" issue raised here on OTN, this has never been the case.

    It always seem to be a thoughtless and impractical "+brain f@rt+" requirement, coming from somewhere (client/user/manager), that simple needs to be done - irrespective of any and all consequences.
  • 47. Re: Sequence Issue
    635471 Expert
    Currently Being Moderated
    Billy  Verreynne  wrote:
    David_Aldridge wrote:

    Sometimes there is a legal requirement that a sequence be gap free.
    In that case, the technical feature to use is not a sequence.
    Yep, I agree.
  • 48. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    David_Aldridge wrote:

    Sometimes there is a legal requirement that a sequence be gap free.
    I have heard it mentioned that there may be legal requirements to produce a gap free consecutive numbering system for certain documents, the one example I recall that appeared to valid was a kind Polish tax receipt, though the only references I could find were in Polish and my Polish is not that good.

    But for arguments sake lets say these cases exist, they are still extremely rare. And if you ever encounter such an exceptional legal requirement, there is absolutely nothing forcing anyone to use an Oracle sequence in the circumstances. You can use a sequence for the key, a time stamp and generate a fixed gap free number in a view, which could be periodically stored in the table.

    Re: Once a month Drop/Recreate Sequence

    Most online ordering systems work this way. The interface that needs to scale to hopefully support many users typically just tells you your order is submitted, the order number is often supplied in a confirmation email.

    So there is no law anywhere that says a sequence must be gap free, there may be laws that state a gap free numbering system needs to be maintained, but in that case you would not use a sequence.
    If you don't like it then go live in a universe where laws are subserviant to software development principles.
    It has nothing to do with software, imagine software does not exist, how would this law work then? Tom Kyte refers to spilling coffee on a book of invoices. Then what happens?
    Sometimes gap free sequences are not a performance killer, because you have very few sessions trying to use them concurrently and the time lost due to serialisation on generating the sequence numbers is a small fraction of the total processing time, or the generation of the sequence values can be performed retrospectively.
    But they always kill scalability, and if you don't need to or want to scale you are probably wasting your, or someone else's, money on Oracle.
  • 49. Re: Sequence Issue
    635471 Expert
    Currently Being Moderated
    3360 wrote:
    David_Aldridge wrote:

    Sometimes there is a legal requirement that a sequence be gap free.
    I have heard it mentioned that there may be legal requirements to produce a gap free consecutive numbering system for certain documents, the one example I recall that appeared to valid was a kind Polish tax receipt, though the only references I could find were in Polish and my Polish is not that good.

    But for arguments sake lets say these cases exist, they are still extremely rare. And if you ever encounter such an exceptional legal requirement, there is absolutely nothing forcing anyone to use an Oracle sequence in the circumstances. You can use a sequence for the key, a time stamp and generate a fixed gap free number in a view, which could be periodically stored in the table.
    Right, and it's appropriate to point out that use on an Oracle sequence is innappropriate for meeting that requirement.

    But the requirements are not that rare: http://www.google.co.uk/search?q=missing+invoice+numbers

    See the following:

    http://www.ukbusinesslabs.co.uk/forums/money-finances-accounting/394-missing-invoice-numbers.html
    http://community.freshbooks.com/forums/viewtopic.php?id=4663
    http://www.jdelist.com/ubb/showflat.php?Cat=0&Number=161547

    It's a very real concern to businesses, and not just for Polish tax receipts.

    >
    Re: Once a month Drop/Recreate Sequence

    Most online ordering systems work this way. The interface that needs to scale to hopefully support many users typically just tells you your order is submitted, the order number is often supplied in a confirmation email.
    Yep -- asynchronous generation of the number. If you don't need it immediately then defer the assignment of the number to a batch process (for example).

    It has nothing to do with software, imagine software does not exist, how would this law work then? Tom Kyte refers to spilling coffee on a book of invoices. Then what happens?
    An awkward interview with auditors, potentially a fine from the statutory authorities. Invoice books do not have backups and redo logs and flashback features that allow disaster recovery though.

    >
    Sometimes gap free sequences are not a performance killer, because you have very few sessions trying to use them concurrently and the time lost due to serialisation on generating the sequence numbers is a small fraction of the total processing time, or the generation of the sequence values can be performed retrospectively.
    But they always kill scalability, and if you don't need to or want to scale you are probably wasting your, or someone else's, money on Oracle.
    I think you mean concurrency, not scalability, but anyhoo ... sometimes your process need not have a high degree of concurrency, and Oracle systems are loaded with processes that are not amenable to concurrency. Direct path insert, for example, is pretty widely used, yet when it gets mentioned on the forum we do not get half a dozen people shouting "hey that kills concurrency! It locks the table!" because we accept that not all processes need concurrency. Not every business process that involves the generation of a series of numbers requires concurrency either.
  • 50. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    David_Aldridge wrote:
    3360 wrote:
    David_Aldridge wrote:

    Sometimes there is a legal requirement that a sequence be gap free.
    I have heard it mentioned that there may be legal requirements to produce a gap free consecutive numbering system for certain documents, the one example I recall that appeared to valid was a kind Polish tax receipt, though the only references I could find were in Polish and my Polish is not that good.

    But for arguments sake lets say these cases exist, they are still extremely rare. And if you ever encounter such an exceptional legal requirement, there is absolutely nothing forcing anyone to use an Oracle sequence in the circumstances. You can use a sequence for the key, a time stamp and generate a fixed gap free number in a view, which could be periodically stored in the table.
    Right, and it's appropriate to point out that use on an Oracle sequence is innappropriate for meeting that requirement.

    But the requirements are not that rare: http://www.google.co.uk/search?q=missing+invoice+numbers
    The claims that there is a requirement are not rare, they come up on these forums regularly.

    Actual valid legal requirements incredibly rare.

    >
    See the following:

    http://www.ukbusinesslabs.co.uk/forums/money-finances-accounting/394-missing-invoice-numbers.html
    Someone on a forum worried about losing numbers asking if it is a problem, two responses saying no problem. This does not describe an actual legal requirement.
    http://community.freshbooks.com/forums/viewtopic.php?id=4663
    Someone on a forum worried about having possibly deleted an invoice saying if their accountant notices missing numbers they go nuts. This does not describe an actual legal requirement.
    http://www.jdelist.com/ubb/showflat.php?Cat=0&Number=161547
    Someone on a forum who's print program is skipping numbers saying it causes pain to the auditors, which again is not a legal requirement.

    >
    It's a very real concern to businesses, and not just for Polish tax receipts.
    The fact that some accountants and auditors like to see sequential numbers does not make it a legal requirement which I still say are incredibly rare. Part of the analysis process is to make clients aware of the cost of these nice to have non-essential features. Often given the choice of You can have the system run at a crawl or buy some antacid for your accountant then they may choose the latter.
    But they always kill scalability, and if you don't need to or want to scale you are probably wasting your, or someone else's, money on Oracle.
    I think you mean concurrency, not scalability, but anyhoo ...
    Systems that do not support concurrency do not scale. Am I missing some fine distinction?
  • 51. Re: Sequence Issue
    635471 Expert
    Currently Being Moderated
    3360 wrote:
    David_Aldridge wrote:
    3360 wrote:
    David_Aldridge wrote:

    Sometimes there is a legal requirement that a sequence be gap free.
    I have heard it mentioned that there may be legal requirements to produce a gap free consecutive numbering system for certain documents, the one example I recall that appeared to valid was a kind Polish tax receipt, though the only references I could find were in Polish and my Polish is not that good.

    But for arguments sake lets say these cases exist, they are still extremely rare. And if you ever encounter such an exceptional legal requirement, there is absolutely nothing forcing anyone to use an Oracle sequence in the circumstances. You can use a sequence for the key, a time stamp and generate a fixed gap free number in a view, which could be periodically stored in the table.
    Right, and it's appropriate to point out that use on an Oracle sequence is innappropriate for meeting that requirement.

    But the requirements are not that rare: http://www.google.co.uk/search?q=missing+invoice+numbers
    The claims that there is a requirement are not rare, they come up on these forums regularly.

    Actual valid legal requirements incredibly rare.
    ...
    The fact that some accountants and auditors like to see sequential numbers does not make it a legal requirement which I still say are incredibly rare. Part of the analysis process is to make clients aware of the cost of these nice to have non-essential features. Often given the choice of You can have the system run at a crawl or buy some antacid for your accountant then they may choose the latter.
    Well it took me about 10 seconds to find this: http://customs.hmrc.gov.uk/channelsPortalWebApp/channelsPortalWebApp.portal?_nfpb=true&_pageLabel=pageLibrary_ShowContent&propertyType=document&id=HMCE_PROD1_027711


    What is the change to the way a VAT invoice is numbered?+

    Currently there is a requirement for a VAT invoice to have an identifying number. The change will require this number to be from a series that is unique and sequential.

    For most businesses this formal, legal change will have no impact on their current invoicing practice and the Frequently Asked Questions cover some common concerns.

    See also the FAQ's:

    What if there is a break in sequence, for example where I cancel an invoice or it is spoiled and never issued to a customer?*

    As long as you retain the cancelled or spoiled invoice in your accounting records, or you can provide an explanation for the break in sequence, this is acceptable.

    Do you think that an explanation that "My software developers told me that such requirements spoil scalability, so i told them that invoice numbers can be skipped" would be acceptable to HM Revenue and Customs?

    So much for very rare -- it applies to every VAT registered business in the United Kingdom.
    But they always kill scalability, and if you don't need to or want to scale you are probably wasting your, or someone else's, money on Oracle.
    I think you mean concurrency, not scalability, but anyhoo ...
    Systems that do not support concurrency do not scale. Am I missing some fine distinction?
    The distinction is not fine. Scalability is a general property of maintaining system performance with increases in the amount of work. Concurrency relates to the interaction between different processes. A single process can exhibit good or bad scalability, and hence be a different matter to concurrency. Direct path insert is highly scalable but non-concurrent because it gets an exclusive lock on the target segment(s).
  • 52. Re: Sequence Issue
    677593 Newbie
    Currently Being Moderated
    create a db table seq_use_table with just 1 column (sequence name )

    Before passing the (sequence name, number) as parameter to the procedure check if the sequence name exists in this table seq_use_table

    use a while loop to check if the sequence name is not in the seq_use_table and call a procedure(sequence name, number)

    at the start of the procedure, write a statement to insert the (sequence_name) into the seq_use_table

    commit;

    call another function(sequence_name, sequence_num ) ---- store these values in a v_array or pl/sql table

    at the end of the procedure, remember to delete the (sequence_name) from the seq_use_table

    return this v_array or pl/sql table from the procedure
    end;

    I hope this should serve your purpose ... before you think of returning 1 seq value in a loop from the procedure to the user , remember the next time the procedure call will fail ..

    instead,return a v_array or pl/sql table containing the values from the procedure before you close the while loop.

    If you think that this pl/sql table or v_array can be overwritten by the next call, add a condition to the while loop to check for this pl/sql table as well ..
  • 53. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    Yes, in the whole range of software systems those that deal with invoices used for VAT purposes are still a tiny minority.

    No one is saying that consecutive numbering is never a requirement, just that those numbers never need to be generated from an Oracle sequence.

    http://customs.hmrc.gov.uk/channelsPortalWebApp/channelsPortalWebApp.portal?_nfpb=true&_pageLabel=pageLibrary_ShowContent&propertyType=document&id=HMCE_PROD1_027711#P67_7729

    >
    I currently use a unique customer reference number as the identifying number on the invoice. If I add an additional consecutive number will this be acceptable?

    Yes, this would have the affect of creating a unique and sequential series.
    >

    I will leave the nitpicking about scalability vs concurrency except to question the need for a scalable system that supports exactly one user reliably.
  • 54. Re: Sequence Issue
    jihuyao Journeyer
    Currently Being Moderated
    Given gap-free,
    and
    assume loading (inserting) non-fixed number of records in each session,
    and
    first come first in? Or faster one complete first?

    create table t_seq(tbl_name, seq# default 1) --partition t_seq if necessary

    1. if first come first in
    run the list in queue one bye one (update squ# column before commit)

    2. if faster one complete first
    create unique constraint on base table,
    loop insert with squ#+i, update squ#, commit
    (rollback on error and try again --timing consuming on too many records and/or too many executions)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 55. Re: Sequence Issue
    William Robertson Oracle ACE
    Currently Being Moderated
    But this value (the 1000) is supposed to be variable. It is not fixed. Which is why I came up with my solution.
    Damn, missed that detail :( If the sequential numbers were always required in blocks of 1000 it would be a lot easier.
  • 56. Re: Sequence Issue
    799683 Newbie
    Currently Being Moderated
    can you try this in one query? didnt test if this will have the mixed problem.

    select seq_name.nextval from dual connect by level <= total_number;
  • 57. Re: Sequence Issue
    635471 Expert
    Currently Being Moderated
    3360 wrote:
    Yes, in the whole range of software systems those that deal with invoices used for VAT purposes are still a tiny minority.
    Well at least that's progress from "extremely rare", and scenarios about spilling coffee on paperwork. There are over 2,000,000 VAT-registered organisations in the UK, and I'm sure that there are millions more in other countries that apply this regulation. This is a critical requirement for every one of them.
    No one is saying that consecutive numbering is never a requirement ...
    Yes, that is what a great many people are saying, and they're jumping to the conclusion that implementing such a requirement will completely kill system performance without understanding the context of its use within the application. It will not do if it is done correctly. It will create a single point of serialisation in one part of a system if a series of gap free numbers needs to be generated in real time by multiple processes. If any of those three are not required then there is no serialisation problem. If all three are required then you design the application to minimise the period of serialisation.

    By all means challenge the use of sequences, and challenge the need for the requirement, but if it has to be done then provide a helpful solution, not some "this will kill system performance" feeding frenzy of FUD.
  • 58. Re: Sequence Issue
    bencol Pro
    Currently Being Moderated
    Oracle Explorer,



    session 1:
    SQL>drop sequence test_seq;
    
    Sequence dropped.
    
    Elapsed: 00:00:00.28
    SQL>
    SQL>create sequence test_seq;
    
    Sequence created.
    
    Elapsed: 00:00:00.01
    SQL>
    SQL>
    SQL>drop table test1;
    
    Table dropped.
    
    Elapsed: 00:00:00.04
    SQL> --give me time to copy & past into session 2
    SQL>exec dbms_lock.sleep(1);
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:01.01
    SQL>
    SQL>create table test1 as select test_seq.nextval seqno
      2  from dual
      3  connect by level <= 1000000;
    
    Table created.
    
    Elapsed: 00:00:20.23
    session 2:
    SQL>create table test2 as select test_seq.nextval seqno
      2  from dual
      3  connect by level <= 1000000;
    
    Table created.
    
    Elapsed: 00:00:20.67
    SQL>select min(seqno), max(seqno) from test1;
    
    MIN(SEQNO) MAX(SEQNO)
    __________ __________
             1    1661994
    
    Elapsed: 00:00:01.14
    SQL>select min(seqno), max(seqno) from test2;
    
    MIN(SEQNO) MAX(SEQNO)
    __________ __________
        292124    2000000
    
    Elapsed: 00:00:00.65
    SQL>
    So you get overlap and you can see the serailistaion of the sequence:

    1 session only:
    SQL>drop table test1;
    
    Table dropped.
    
    Elapsed: 00:00:00.09
    SQL>create table test1 as select test_seq.nextval seqno
      2  from dual
      3  connect by level <= 1000000;
    
    Table created.
    
    Elapsed: 00:00:09.98
    
    SQL>create table test2 as select test_seq.nextval seqno
      2  from dual
      3  connect by level <= 1000000;
    
    Table created.
    
    Elapsed: 00:00:10.04
    SQL>select min(seqno), max(seqno) from test1;
    
    MIN(SEQNO) MAX(SEQNO)
    __________ __________
             1    1000000
    
    Elapsed: 00:00:00.93
    SQL>select min(seqno), max(seqno) from test2;
    
    MIN(SEQNO) MAX(SEQNO)
    __________ __________
       1000001    2000000
    So in both cases it take about 20 seconds to create 2 tables - it isn't quicker to parallelise this. But in the first you don't get a full set of consecutive numbers in each table
  • 59. Re: Sequence Issue
    635471 Expert
    Currently Being Moderated
    I've written my consolidated thoughts on the topic here http://oraclesponge.wordpress.com/2010/09/24/generating-a-gap-free-series-of-numbers-not-always-a-problem/ , and thought I might as well take the liberty of linking to it. Please feel free to provide feedback and comments either there or here.
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points