{"id":91823,"date":"2021-07-14T19:26:48","date_gmt":"2021-07-14T19:26:48","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91823"},"modified":"2025-10-31T11:52:48","modified_gmt":"2025-10-31T11:52:48","slug":"typical-uses-oracle-sequences","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/typical-uses-oracle-sequences\/","title":{"rendered":"Typical uses of Oracle sequences"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Oracle sequences: The basics<\/a><\/li>\n
  2. Oracle sequences: RAC<\/a><\/li>\n
  3. Typical uses of Oracle sequences<\/a><\/li>\n
  4. Oracle sequences \u2013 12c features including \u201cidentity\u201d<\/a>\u00a0<\/li>\n<\/ol>\n\n

    So far, in this mini-series on sequences, I have described the basic mechanics of sequences in single-instance and multi-instance (RAC) systems. I\u2019ve explained the significance of setting the cache<\/em><\/strong> size in both cases and the order<\/em><\/strong> option for RAC. I\u2019ve demonstrated how using sequences as synthetic\/surrogate keys can lead to contention on the \u201cright-hand\u201d<\/em>\/\u201chigh-value\u201d<\/em> block of supporting indexes unless you take defensive actions. I\u2019ve also shown how one of the typical strategies has been embedded in recent versions of Oracle in the scale<\/em><\/strong> and extend<\/em><\/strong> options. In this article, I\u2019ll cover the most typical uses of Oracle sequences and highlight a couple of details of their behaviour that can cause some confusion.<\/p>\n

    Basic options<\/h2>\n

    There are essentially only two things you can do with a sequence \u2013 you can ask for the \u201cnext value\u201d by referencing {sequence}.nextval<\/code> or ask for the \u201ccurrent value\u201d by referencing {sequence}.currval<\/code>. <\/em><\/strong>By making a statement that simple, I\u2019ve already introduced a trap. The nextval<\/code> for a sequence is a system-level request, the currval<\/code> for a sequence is a session-level request. While nextval<\/code> is a request for Oracle to generate the next available value according to the sequence definition, currval<\/code> is a request to repeat the most recent value that the current session got on its most recent call to nextval<\/code>. So, for example, this is what happens if you start a new SQL*Plus session and immediately call for {sequence}.currval<\/code>:<\/p>\n

    SQL> select s1.currval from dual;\nselect s1.currval from dual\n       *\nERROR at line 1:\nORA-08002: sequence S1.CURRVAL is not yet defined in this session<\/pre>\n
    The session hasn\u2019t yet called (and saved locally) s1.nextval<\/code>, which is why it says that s1.currval<\/code> is not yet defined. (Personally, I think the error message could have been more explicit \u2013 e.g. commenting that there has been no previous call to s1.nextval<\/code> in the session.) Reconnect, do a couple of calls for s. nextval<\/code>,<\/em><\/strong> then a couple for s1.currval<\/code>,<\/em><\/strong> and this is what happens:<\/p>\n
    SQL> select s1.next<\/strong>val from dual;\n   NEXTVAL\n----------\n         3\n1 row selected.\nSQL> select s1.next<\/strong>val from dual;\n   NEXTVAL\n----------\n         4\n1 row selected.\nSQL> select s1.curr<\/strong>val from dual;\n   CURRVAL\n----------\n         4\n1 row selected.\nSQL> select s1.curr<\/strong>val from dual;\n   CURRVAL\n----------\n         4\n1 row selected.<\/pre>\n
    I\u2019ve used a select from dual<\/code> to get a single sequence value but you can do multi-row selects to get a stream of sequence values, and you can also use simple assignments to get a value, e.g:<\/p>\n
    SQL> declare\n  2          n1 number(6,0);\n  3  begin\n  4          n1 := s1.nextval;\n  5          dbms_output.put_line(n1);\n  6  end;\n  7  \/\n5<\/strong>\nPL\/SQL procedure successfully completed.<\/pre>\n
    If you enable tracing on this anonymous pl\/sql block, though, you will find that behind the scenes, Oracle will have executed a simple select s1.nextval from dual<\/code> to acquire the value.<\/p>\n
    The basic rule of sequences is that you write code to generate a \u201crowsource\u201d, and the sequence generator is called once per row; this produces results that aren\u2019t always quite what you anticipate. Consider the position where I start a new session and continue using sequence s1<\/code> just after the point where I ended in the previous example, and execute the following three statements:<\/p>\n
    SQL> select s1.currval, s1.nextval from dual;\nSQL> select s1.nextval, s1.currval from dual;\nSQL> select s1.nextval, s1.nextval, s1.nextval from dual;<\/pre>\n
    Looking at the first statement, which (apparently) calls currval<\/code> before it calls nextval<\/code>,<\/em><\/strong> you might expect it to fail with the ORA-08002 error, but it doesn\u2019t.<\/p>\n
    Looking at the second statement, you might expect the call to currval<\/code> to report the previous value acquired by this session while nextval<\/code> reports the next value from the global cache, but it doesn\u2019t.<\/p>\n
    Looking at the last statement, you might expect to see three consecutive sequence values, but you won\u2019t.<\/p>\n
    The results of the three statements are as following:<\/p>\n
    SQL> select s1.currval, s1.nextval from dual;\n   CURRVAL    NEXTVAL\n---------- ----------\n         6          6\nSQL> select s1.nextval, s1.currval from dual;\n   NEXTVAL    CURRVAL\n---------- ----------\n         7          7\nSQL> select s1.nextval, s1.nextval, s1.nextval from dual;\n   NEXTVAL    NEXTVAL    NEXTVAL\n---------- ---------- ----------\n         8          8          8<\/pre>\n
    There is one call to the sequence generator for each row which is why 8 appears three times. If you include both currval<\/code> and nextval<\/code> in the select list, then nextval<\/code> is called, and the return value used for both the nextval<\/code> and the currval<\/code>.<\/p>\n
    Some of the questions people ask about the \u201cunexpected\u201d behaviour of sequences can be answered very easily once the connection between \u201crows selected\u201d<\/em> and \u201ccalls to nextval\u201d<\/em> is clear. However, this doesn\u2019t mean that \u201cif you can write a select clause you can select a sequence\u201d; there are cases where your code will run into the exception: ORA-02287: sequence number not allowed here<\/em>, for example:<\/p>\n
    SQL> create or replace view v1 \n  2  as\n  3  select object_name, owner, s1.nextval\n  4  from t1\n  5  \/\nselect object_name, owner, s1.nextval\n                              *\nERROR at line 3:\nORA-02287: sequence number not allowed here<\/pre>\n
    There are also cases where adding a sequence to the select list has a much greater effect than just being \u201canother column.\u201d Distributed queries, for example, may change their execution plan quite dramatically because the addition of a sequence has changed a query from \u201cremote only\u201d to genuinely distributed.<\/p>\n
    Sequences and DML<\/h2>\n
    The most common use of a sequence is probably as the source of a surrogate (or synthetic) key, so you may see code like the following in an ETL or other large batch process:<\/p>\n
    insert into production_table \nselect  s1.nextval, \u2026\nfrom    staging_table\nwhere\t   ...<\/pre>\n
    An important point to be aware of when you do this type of thing is that sequences and parallelism don\u2019t co-operate very well. It would be very easy to decide that you needed to increase the speed at which you were generating data and that simply using parallel execution would help and write something like:<\/p>\n
    insert  \/*+ parallel(6) *\/ into production_table \nselect  s1.nextval, \u2026\nfrom    staging_table\nwhere\t   ...<\/pre>\n
    A common mistake that people make at this point is to forget that parallel DML is not enabled by default, so if you want the insert to operate with DOP (degree of parallelism) 6, then you also need to execute<\/p>\n
    alter session enable parallel dml;<\/pre>\n
    (and don\u2019t forget to disable it afterwards).<\/p>\n
    However, having flagged up this reminder on parallel DML, I\u2019m now going to point out that if your code uses sequences, you might be in exactly the position where you want the select to run parallel, but the insert to run serially. Remember that sequence.nextval<\/code> has to update a specific row in the seq$<\/code> table from time to time, and (more importantly) every call to nextval<\/code> has to access the dictionary cache entry for that particular sequence. That means competition for dictionary cache mutexes (or latches for older versions of Oracle). You may find that the nominal benefit of parallel execution disappears in hugely increased CPU consumption due to contention.<\/p>\n
    End-user DML<\/h3>\n
    In high-precision code (typically end-user facing( you may do something which requires re-use of a primary key as a foreign key to another table: orders<\/em><\/strong> and order_lines,<\/em><\/strong> for example, and there have been a few variations in method over the years. For example:<\/p>\n
    select s1.nextval into :local_variable from dual;\ninsert into orders (id, \u2026) values(:local_variable, \u2026);\ninsert into order_lines(id_ord, line, \u2026) values(:local_variable, \u2026)<\/pre>\n
    Of course, this type of approach might have been necessary when it wasn\u2019t possible to use a sequence nextval<\/code> as the default for a column. But now we can, so we could have something like:<\/p>\n
    create sequence s1 start with 1e6 cache 1e3;\ncreate table orders (\n        id number(8,0) default s1.nextval primary key, \n        date_made date not null\n);\ncreate table order_lines(\n        id_ord     number(8,0) not null references orders, \n        line#      number(4,0) not null, \n        id_product number(8,0) not null\n);\ninsert into orders     (date_made) values (sysdate);\ninsert into order_lines(id_ord, line#, id_product) \n                                   values(s1.currval, 1, 999);<\/pre>\n
    This does require the programmer to know that the orders table has its primary key generated by a call to a sequence called s1. <\/em><\/strong>Be aware of Oracle\u2019s syntax, which allows you to call sequence.currval<\/code> after (even implicitly) you\u2019ve called sequence.nextval<\/code>. A slightly more generic, or programmer-friendly, variant might return the generated id to the client code:<\/p>\n
    variable m_id number\ninsert into orders (date_made) values (sysdate) returning id into :m_id;\ninsert into order_lines(id_ord, line#, id_product) values(:m_id, 1, 999);<\/pre>\n
    If you start using the 12c \u201cidentity\u201d type, you probably have to adopt this approach to retrieve the value you\u2019ve inserted. You\u2019re not supposed to know that, under the covers, Oracle is simply using a sequence to supply a value, and you certainly should not assume you can work out the name of that sequence and call for its currval<\/code>.<\/p>\n
    create table orders (\n        id number(8,0) \n                generated always as identity start with 1e6 cache 1e3\n                primary key,\n        date_made date not null\n);<\/pre>\n
    The Merge Command<\/h3>\n
    An increasingly popular strategy for updating one table from another is to use the merge<\/code> command, which has the following skeleton:<\/p>\n
    merge into target \nusing source\non target.cols = source.cols\nwhen matched then \n\tupdate set {target_columns} = {source columns} where \u2026\n\tdelete where \u2026\nwhen not matched then\n\tInsert ({target columns}) values({source columns}) where \u2026<\/pre>\n
    A couple of points to watch out for in general with the merge command:<\/p>\n