{\rtf1\ansi \deff5\deflang1033{\fonttbl{\f1\froman\fcharset2\fprq2 Symbol;}{\f4\froman\fcharset0\fprq2 Times New Roman;}{\f5\fswiss\fcharset0\fprq2 Arial;}{\f11\fmodern\fcharset0\fprq1 Courier New;}
{\f97\fswiss\fcharset0\fprq2 Tahoma;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;
\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\widctlpar \f5\lang2057 \snext0 Normal;}{
\s1\qc\widctlpar\dxfrtext187\dfrmtxtx187\dfrmtxty187 \b\f5\ul\lang2057 \sbasedon0\snext0 heading 1;}{\s2\widctlpar \b\f5\ul\lang2057 \sbasedon0\snext0 heading 2;}{\s3\widctlpar \b\f5\lang2057 \sbasedon0\snext15 heading 3;}{\*\cs10 \additive 
Default Paragraph Font;}{\s15\fi-720\li720\widctlpar \f5\lang2057 \sbasedon0\snext15 Normal Indent;}{\s16\widctlpar \f5\fs20\lang2057 \sbasedon0\snext16 endnote text;}{\s17\widctlpar\tqc\tx4819\tqr\tx9071 \f5\lang2057 \sbasedon0\snext17 header;}{
\s18\li1440\widctlpar \b\f5\lang2057 \sbasedon0\snext18 footnote text;}{\s19\fi-720\li1440\widctlpar \f5\lang2057 \sbasedon15\snext19 Double Indent;}{\s20\widctlpar \b\f5\ul\lang2057 \sbasedon0\snext20 Action;}{\s21\sb120\sa120\widctlpar \b\f5\lang2057 
\sbasedon0\snext0 caption;}{\s22\widctlpar \f11\fs16\lang2057 \sbasedon0\snext22 program;}{\s23\widctlpar \i\f5\lang2057 \sbasedon0\snext23 Body Text;}{\*\cs24 \additive\ul\cf2 \sbasedon10 Hyperlink;}{
\s25\sb60\sa60\widctlpar\tx480\tx960\tx1440\tx1920\tx2400\tx2880\tx3360\tx3840\tx4320 \f11\fs20 \snext25 macro;}{\s26\widctlpar \cbpat9 \f97\lang2057 \sbasedon0\snext26 Document Map;}}{\info{\title Relate article - Partition Views}{\author Jonathan Lewis}
{\operator Jonathan Lewis}{\creatim\yr1999\mo7\dy1\hr8\min50}{\revtim\yr1999\mo8\dy19\hr14\min29}{\printim\yr1998\mo6\dy7\hr9\min53}{\version8}{\edmins18}{\nofpages5}{\nofwords1745}{\nofchars9948}{\*\company JL Computer Consultancy}{\vern57443}}
\paperw11909\paperh16834\margl1440\margr1440\margt1080\margb1080 \widowctrl\ftnbj\aendnotes\ftnnrlc\aftnnar\prcolbl\cvmme\otblrul\brkfrm\swpbdr\hyphcaps0\fracwidth \fet0\sectd \sbknone\linex0\headery1080\footery1080\colsx709\endnhere {\*\pnseclvl1
\pnucrm\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang{\pntxta )}}{\*\pnseclvl5
\pndec\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang
{\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}\pard\plain \s1\qc\widctlpar\dxfrtext187\dfrmtxtx187\dfrmtxty187 \b\f5\ul\lang2057 What Next ?  All about sequences.
\par \pard\plain \widctlpar \f5\lang2057 {\i 
\par }\pard\plain \s23\widctlpar \i\f5\lang2057 
Avoiding the temptation to rattle on about some of the wonderful new features of Oracle 8.1, this article focuses on SEQUENCES .  Although sequences have been a part of Oracle for many years, it is surprising how many mis
conceptions are still floating around the community about how they work.  This article aims to be your definitive guide to sequences for all versions of Oracle at least from 7.3.3 to 8.1.5.
\par \pard\plain \widctlpar \f5\lang2057 {\i 
\par }\sect \sectd \sbknone\linex0\headery1080\footery1080\cols2\colsx709\endnhere \pard\plain \widctlpar \f5\lang2057 {\b\ul What is a Sequence ?
\par }\pard \widctlpar There are two answers to this question, or rather there are really two questions:  what does a sequence look like to to a user, and what does a sequence look like internally to Oracle.
\par \pard \widctlpar 
\par \pard \widctlpar A sequence is an object that allows a user to call for a meaningless number with the knowledge that no other user
 will ever be given the same number (except for the special case of 'CYCLE' sequences) and with the benefit that there will be virtually no delay before the number becomes available.  
\par \pard \widctlpar 
\par \pard \widctlpar In most cases an Oracle sequence object behaves as you would expect a \lquote real-life\rquote  sequence number to behave: each time you say \lquote next please\rquote 
 the next consecutive integer appears;  however there are many variations to sequences that make this association very mis-leading.
\par \pard \widctlpar 
\par \pard \widctlpar Internally a sequence consists of a row in the {\b seq$} dictionary table that records its definition and current high-water mark, and a cached component in the SGA (seen through the dynamic performance view {\b v$_sequence}
) which repeats this information and also holds the next value to be supplied to an end-user request.  
\par \pard \widctlpar 
\par \pard \widctlpar 
The SGA cache controls the values passed out in response to user requests for sequences, and updates the data dictionary with high-water marks from time to time, so there is a point at which serialisation can occur (more on this later).   
\par \pard \widctlpar 
\par \pard \widctlpar Because the mechanism is internal to Oracle it is very efficient and bypasses the normal locking contention that appears with the traditional end-user coded \lquote tables of sequence values\rquote 
 that had to be implemented in very early versions of Oracle.
\par \pard \widctlpar 
\par \pard \widctlpar {\b\ul Creating a Sequence:
\par }\pard \widctlpar The full syntax for creating a sequence is shown in fig 1. 
\par \pard \widctlpar 
\par \pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy163\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 Create sequence XXX
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy163\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 Start with \{integer\}
\par increment by \{integer\}
\par maxvalue \{integer\} / nomaxvalue 
\par minvalue \{integer\} / nominvalue
\par cycle / nocycle
\par cache \{integer\} / nocache
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy163\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 order / noorder
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy163\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 
\par \pard\plain \s21\sb120\sa120\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy163\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \b\f5\lang2057 {\fs16 Figure }{\field{\*\fldinst {\fs16  SEQ Figure \\* ARABIC }}{\fldrslt {\fs16\lang1024 1}
}}{\fs16 : Syntax to create a sequence
\par }\pard\plain \widctlpar \f5\lang2057 
\par \pard \widctlpar But the shortest code you can use to create a sequence is simply: 
\par \pard \widctlpar 
\par \pard \fi720\widctlpar {\b create sequence my_seq;}
\par \pard \widctlpar 
\par \pard \widctlpar This will result in a sequence with the  default values as shown in fig.2 - In general it is a bad idea to create sequences like this, as it can lead to performance problems. 
\par 
\par \pard \widctlpar Fortunately there is an \lquote {\b alter sequence\rquote } command that allows you to change all aspects of a sequence apart from the starting value.
\par \pard \widctlpar 
\par 
\par 
\par 
\par \pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1667\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 Create sequence my_seq
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1667\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 Start with 1
\par increment by 1
\par maxvalue nomaxvalue 
\par minvalue 1
\par nocycle
\par cache 20
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1667\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 noorder
\par 
\par }\pard\plain \s21\sb120\sa120\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1667\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \b\f5\lang2057 {\fs16 Figure }{\field{\*\fldinst {\fs16  SEQ Figure \\* ARABIC }}{\fldrslt {\fs16\lang1024 
2}}}{\fs16  The defaults for 'create sequence'}
\par \pard\plain \widctlpar \f5\lang2057 There are some fairly obvious limits to the \lquote {\b alter sequence\rquote } command:  you may not,  for example, alter the {\b maxvalue} to be a value smaller than the current value of the sequence.
\par \pard \widctlpar 
\par \pard \widctlpar {\b\ul Using a Sequence:
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 declare
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 m_var number;
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 begin
\par }\pard \fi720\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 m_var := jpl_demo.nextval;
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 end;
\par 
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 pls-00357: (not allowed in this context)
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 
\par }\pard\plain \s21\sb120\sa120\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy397\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \b\f5\lang2057 {\fs16 Figure }{\field{\*\fldinst {\fs16  SEQ Figure \\* ARABIC }}{\fldrslt {\fs16\lang1024 3
}}}{\fs16  Illegal use of nextval/currval}
\par \pard\plain \widctlpar \f5\lang2057 There are only two requests you can make to a sequence object:  \lquote give me the next available number\rquote  and \lquote remind me what that was again\rquote ;  these are the {\b nextval} and {\b currval}
 calls respectively.
\par \pard \widctlpar   
\par \pard \widctlpar The {\b nextval} request goes to the global cache to get the next available sequence value and copies it to the session\rquote s local memory.  
\par \pard \widctlpar 
\par \pard \widctlpar The {\b currval} request goes back to the session\rquote s local memory and repeats the value it finds there.
\par \pard \widctlpar 
\par \pard \widctlpar An important point to remember when using the {\b nextval / currval} cycle is that a session cannot call {\b currval} before its first call to {\b nextval} otherwise the session gets Oracle error 8002 : 
\par \pard\plain \s25\sb60\sa60\widctlpar\tx480\tx960\tx1440\tx1920\tx2400\tx2880\tx3360\tx3840\tx4320 \f11\fs20 {\lang2057 sequence XXX.CURRVAL is not yet defined in this session.
\par }\pard\plain \s17\widctlpar \f5\lang2057 There are some restrictions on the exact use of the {\b nextval} and {\b currval}
 calls.  Loosely speaking, the call must be associated with an SQL statement, although some uses (e.g. in subqueries, in snapshots, in aggregate queries) are invalid. 
\par \pard \s17\widctlpar 
\par 
\par \pard \s17\widctlpar Fig 3 gives a few  examples of legal use, whilst fig. 4 shows the most common invalid use of nextval/currval. 
\par \pard\plain \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy515\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \f5\lang2057 {\f4 select my_seq.nextval from dual;
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy515\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 
\par select my_seq.nextval
\par into :m_variable
\par from dual;
\par 
\par insert into parent_tab 
\par values (my_seq.nextval);
\par 
\par insert into child_tab 
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy515\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 select my_seq.currval from big_table;
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy515\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 
\par }\pard\plain \s21\sb120\sa120\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy515\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \b\f5\lang2057 {\fs16 Figure }{\field{\*\fldinst {\fs16  SEQ Figure \\* ARABIC }}{\fldrslt {\fs16\lang1024 4
}}}{\fs16  Legal use of nextval/currval}
\par \pard\plain \s17\widctlpar \f5\lang2057 
\par \pard\plain \widctlpar \f5\lang2057 {\b\ul 
\par 
\par }\pard \widctlpar {\b\ul Problems with Sequences:
\par }\pard \widctlpar Perhaps the commonest use for a sequence is to generate a meaningless (surrogate) primary key.  Sequences seem to be perfect for this: no-one gets the same value, there is (usually) no contention for sequence values, and the parent
/child association can be handled easily through the nextval/currval calls.  
\par \pard \widctlpar 
\par \pard \widctlpar Prior to the introduction of sequences, developers would create a table of the form (object_name, seq_no), and execute code similar to the sample in fig. 5. 
\par \pard \widctlpar 
\par \pard \widctlpar The problem with this strategy is that it serialises the creation of data \endash  the user who gets a sequence number from a sequence table is locking that row and stopping anyone else from getting a value for the same sequence. 
\par \pard \widctlpar 
\par \pard \widctlpar 
This serialisation is probably the reason why Oracle introduced its sequences.  An Oracle sequence type is not associated with a transaction, so one session does not have to wait for another to commit before acquiring the next available value.  
\par \pard \widctlpar 
\par \pard \widctlpar But this feature of sequences leads to the commonest complaint about the way they behave \endash  {\i sequence numbers can be lost}. 
\par \pard\plain \s17\widctlpar \f5\lang2057 
\par \pard\plain \s2\widctlpar \b\f5\ul\lang2057 Losing Sequence Numbers
\par \pard\plain \widctlpar \f5\lang2057 There are three ways to lose sequence numbers.  The first is to throw them away, the second is to lose them in cache flushing, the third is to lose the entire SGA when the database aborts.
\par \pard \widctlpar 
\par \pard \widctlpar Remember that the \lquote active\rquote 
 part of the sequence is cached in the SGA, and consists of the definition, the current high-water mark, and the next value to be allocated.  If your session requests a sequence number (as the primary key for a new row perhaps), then \lquote the next val
ue\rquote  is incremented.  If you choose to do nothing with that value (or perhaps rollback the transaction that was going to use it) then there is no mechanism for \lquote putting the value back\rquote 
.  The sequence number simply disappears - unlike the equivalent action on a \lquote sequence table\rquote  where a rollback would undo the change and make the previous value visible again.
\par \pard \widctlpar 
\par \pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1387\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 select seq_no + 1
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1387\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 from seq_table
\par where object_name = \lquote XXX\rquote 
\par for update of seq_no nowait;
\par 
\par update seq_table
\par set seq_no = seq_no + 1
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1387\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 where object_name = \lquote XXX\rquote ;
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1387\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 
\par }\pard\plain \s21\sb120\sa120\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx1500\posy1387\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \b\f5\lang2057 {\fs16 Figure }{\field{\*\fldinst {\fs16  SEQ Figure \\* ARABIC }}{\fldrslt {\fs16\lang1024 
5}}}{\fs16  Sequences through tables}
\par \pard\plain \widctlpar \f5\lang2057 If the database crashes (or you issue a shutdown abort) then any values between \lquote the next value\rquote  and the high-water mark are lost.    In fact earlier ve
rsions of Oracle would lose these values even during an immediate or normal shutdown,  it is only relatively recently that the number cached as \lquote the next value\rquote  was written back to {\b seq$} as the latest high-water mark.
\par \pard \widctlpar 
\par \pard \widctlpar Finally if the SGA caching area for sequences (defined by the init.ora parameter {\b\i sequence_cache_entries}) is too small then a sequence could be flushed from the SGA and lose all the values between \lquote the next value\rquote 
 and the high-water mark.  In fact I have found that this init.ora parameter is no
t a strict limit in 8.0, and is hidden anyway in 8.1.  For version 7, though, you may as well set this parameter larger than the maximum number of sequences used in the database.
\par \pard \widctlpar 
\par \pard \widctlpar Some developers think that they are losing sequences numbers because calls to {\b nextval}
 do not give them the value they expect. Of course, since the sequence is centrally cached in the SGA, all that has happened is that another session has been asking for the same sequence at the same time.
\par \pard\plain \s17\widctlpar \f5\lang2057 
\par \pard\plain \widctlpar \f5\lang2057 Another odd feature of sequences and \lquote missing\rquote  sequence numbers appears in the Oracle Parallel Server.  
\par \pard \widctlpar 
\par \pard \widctlpar Each instance holds it own cache of sequence values \endash  so in a 3-instance system, you may find instance A has cached 1 to 20, instance B has cached 21 to 40, and instance C has cache 41 to 60, with the high-water mark in {\b seq$}
 necessarily set to 60.
\par \pard \widctlpar 
\par \pard \widctlpar If instance A is the busiest user of the sequence, then it may the first instance to hit its {\i cached} high-water mark (20).  At this point it reads {\b seq$}, bumps the database high-water mark to 80, and 
sets its own cache to hold values 61 to 80.  Not only is this a big surprise to the user on instance A, but also many values between 20 and 60 could be lost if instances B and C now shut down.
\par \pard \widctlpar 
\par \pard\plain \s2\widctlpar \b\f5\ul\lang2057 Performance Issues
\par \pard\plain \widctlpar \f5\lang2057 Oracle sequences are much faster than user-defined sequences but they still have an inherent performance limitation \endash  to ensure uniqueness, the database has to hold a highwater mark.
\par \pard \widctlpar 
\par \pard \widctlpar The speed of sequences is largely dependent on the frequency with which this highwater mark is updated, and in high-performance system you need to think about this carefully.  
\par \pard \widctlpar 
\par \pard \widctlpar The default CACHE value for a sequence is 20, which means that after every 20 calls for {\b nextval} Oracle has to update {\b seq$} and refresh its cache.  
\par \pard \widctlpar 
\par \pard \widctlpar If you are creating data at a very high rate (e.g. create table as select with a sequence number for the primary key) this update of {\b seq$} (with its rollback and redo logging) can become the most expensive part of the operation.

\par \pard \widctlpar 
\par \pard \widctlpar As a demonstration, fig. 6 shows a simple CTAS statement, and a list showing the effects on the execution time of changing the cache size.
\par \pard \widctlpar 
\par 
\par 
\par 
\par 
\par 
\par 
\par \pard \widctlpar 
\par \pard \widctlpar The effects of a small CACHE size on a Parallel Server system are, of course, much worse since the block containing the relevant row from seq$ will be pinged from instance to instance as the row is updated.  (
And this will affect at the very least all the other sequences in the same block).
\par \pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy254\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 Create table jpl_ctas
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy254\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 unrecoverable
\par as
\par select jpl_demo.nextval
\par from big_table 
\par where rownum <= 10000
\par 
\par }{\f4\ul Cache Size}{\f4 \tab \tab }{\f4\ul Time (secs)}{\f4 
\par 1,000\tab \tab \tab 1.602
\par {\pntext\pard\plain 20\tab}}\pard \fi-2160\li2160\widctlpar\box\brdrs\brdrw15\brsp20 \tx2160\pvpara\phpg\posx6396\posy254\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0{\*\pn \pnlvlbody\pndec\pnstart20\pnindent2160\pnhang}{\f4 3.38
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy254\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 nocache\tab \tab 35.30
\par }\pard \widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy254\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 {\f4 
\par }\pard\plain \s21\sb120\sa120\widctlpar\box\brdrs\brdrw15\brsp20 \pvpara\phpg\posx6396\posy254\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0 \b\f5\lang2057 {\fs16 Figure }{\field{\*\fldinst {\fs16  SEQ Figure \\* ARABIC }}{\fldrslt {\fs16\lang1024 6
}}}{\fs16  Effects of different CACHE sizes}
\par \pard\plain \widctlpar \f5\lang2057 
\par \pard \widctlpar Clearly then, it is important to set a large CACHE value - but there is another trap on Parallel Server systems in the ORDER / NOORDER option.  
\par \pard \widctlpar 
\par The purpose of this setting is to 
\par \pard \widctlpar 
ensure that sequence values are assigned in order of the request (not that I have ever seen sequence numbers allocated out of order); but I have already pointed out that in Parallel Server systems each instance gets its own range of values.  
\par \pard \widctlpar 
\par \pard \widctlpar So how does Oracle manage to hand out sequence numbers in order if each instance has its own set of values ?  The answer is - they don\rquote 
t.  For parallel server systems ORDER and CACHE are incompatible options, and the sequence is NOT CACHED - which could result in an awful lot of pinging.
\par \pard \widctlpar 
\par \pard\plain \s20\widctlpar \b\f5\ul\lang2057 
\par 
\par 
\par Conclusion:
\par \pard\plain \widctlpar \f5\lang2057 I said in the heading for this article that I wanted to provide the definitive guide to sequences.  Perhaps that was a little optimistic for 2,000 words. I have not covered descending sequences, uses of CYCLIC 
sequences, the standard trick to reset a sequence and many other features, but I hope that I have made 6 points clear.
\par \pard\plain \s17\widctlpar \f5\lang2057 
\par {\pntext\pard\plain\f1 \'b7\tab}\pard\plain \fi-360\li360\widctlpar\tx360{\*\pn \pnlvlblt\pnf1\pnstart1\pnindent360\pnhang{\pntxtb \'b7}}\f5\lang2057 Each sequence has a small, instance-global cache of values to be distributed when {\b nextval} is called.

\par \pard \widctlpar{\*\pn \pnlvlcont\pndec }
\par {\pntext\pard\plain\f1 \'b7\tab}\pard \fi-360\li360\widctlpar\tx360{\*\pn \pnlvlblt\pnf1\pnstart1\pnindent360\pnhang{\pntxtb \'b7}}You cannot call {\b currval} until you have called {\b nextval}.
\par \pard \widctlpar{\*\pn \pnlvlcont\pndec }
\par {\pntext\pard\plain\f1 \'b7\tab}\pard \fi-360\li360\widctlpar\tx360{\*\pn \pnlvlblt\pnf1\pnstart1\pnindent360\pnhang{\pntxtb \'b7}}You have to use SQL to access sequence numbers \endash  even in PL/SQL blocks.
\par \pard \widctlpar{\*\pn \pnlvlcont\pndec }
\par {\pntext\pard\plain\f1 \'b7\tab}\pard \fi-360\li360\widctlpar\tx360{\*\pn \pnlvlblt\pnf1\pnstart1\pnindent360\pnhang{\pntxtb \'b7}}If you want to minimise \lquote lost\rquote  values, adjust {\b sequence_cache_entries}.
\par \pard\plain \s17\widctlpar{\*\pn \pnlvlcont\pndec }\f5\lang2057 
\par {\pntext\pard\plain\f1 \'b7\tab}\pard\plain \fi-360\li360\widctlpar\tx360{\*\pn \pnlvlblt\pnf1\pnstart1\pnindent360\pnhang{\pntxtb \'b7}}\f5\lang2057 You need to consider the rate at which you use the sequence and pick a suitable value for the CACHE size.

\par \pard \widctlpar{\*\pn \pnlvlcont\pndec }
\par {\pntext\pard\plain\f1 \'b7\tab}\pard \fi-360\li360\widctlpar\tx360{\*\pn \pnlvlblt\pnf1\pnstart1\pnindent360\pnhang{\pntxtb \'b7}}Do not use the ORDER option on Parallel Server systems unless you are confident that you can put up with the side-effects.

\par \pard \widctlpar \sect \sectd \sbknone\linex0\headery1080\footery1080\colsx709\endnhere \pard\plain \widctlpar \f5\lang2057 {\i 
\par }\pard \widctlpar {\i 
Jonathan Lewis is a freelance consultant with 14 years experience of Oracle.  He specialises in short-term contracts advising on strategic use of Oracle, physical database design, trouble-shooting and training. In his spare time, he is the chairman of the
 UNIX SIG of the UKOUG.  This article, and many others describing best practices with Oracle, can be found on his web-site www.jlcomp.demon.co.uk 
\par }\pard \widctlpar {\i 
\par }}