BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  Oracle Tips by Burleson

Sequences

A sequence is a database object that provides a number when requested.  Like a view, a sequence is just the definition and the current number.  When a sequence is asks for a number, it looks at the current number and provides the next number as per the sequence definition.

SQL> create sequence pubs1;

Sequence created.

SQL> select pubs1.nextval from dual;

   NEXTVAL
----------
         1

SQL> select pubs1.nextval from dual;

   NEXTVAL
----------
         2

In the example above, we created a sequence called pubs1.  We use the NEXTVAL function to retrieve the next value.  You can get the current value by calling the CURRVAL function.   You must call the NEXTVAL function before calling the CURRVAL function, or you will get an error.  There is no current value to the sequence until the next value has been called at least once.

When you create the sequence, you have a lot of flexibility as to how the sequence generates the next number.

SQL> create sequence pubs2
  2  start with 8
  3  increment by 2
  4  maxvalue 10000
  5  cycle
  6  cache 5; 

Sequence created.

SQL> select pubs2.nextval from dual; 

   NEXTVAL
----------
         8
 

The sequence pubs2 will start at number eight and increment by twos to 10000 and then start over.  Let’s cover each of their parameters:

§        START WITH  – defines the first number of the sequence.  Default is one.

§        INCREMENT  – defines how many to add to get the next values.  Default is one.

§        MINVALUE  – defines the lowest value of the sequence when the sequence is created to count down (increment by a minus number).

§        MAXVALUE  – defines the largest value of the sequence.  Default is 10E23. 

§        CYCLE/NOCYCLE – tells the sequence to start over once it reaches the maxvalue or minvalue.  The default is NOCYCLE.

§        CACHE/NOCACHE – tells the database how many numbers to cache in memory.  The default is 20.  So, if the sequence is starting at one, the database will cache 1..20 and set the sequence at 21.  The database will answer the NEXTVAL request from the numbers in memory.  Once those numbers are used, the database will load 20 more numbers in memory.  If the database is shutdown, all sequence numbers in memory will be lost.  When the database restarts, it will look at the number for the sequence and load the next 20 into memory, setting the sequence forward by 20.  This improves response but will lose some numbers.  Setting the NOCACHE will cause the database not to cache any numbers in memory.

Sequences are relatively simple objects designed to provide a series of numbers.  No call to NEXTVAL will result in the same number unless the sequence cycles.  One of the main uses of a sequence is to provide a primary key for a table where the rows do not naturally define a unique number.  In our AUTHOR table, the primary key is author_key, and it is formatted as A101, A102, etc.  I can use a sequence to generate this key.

SQL> select pubs2.nextval from dual;

   NEXTVAL
----------
        12 

SQL> insert into author values
  2  (
  3    'A'||to_char (pubs2.nextval + 100),
  4    'thumbmasher',
  5    'philbert',
  6   '457-934-2642',
  7   '1320 leaning tree ln',
  8   'pie town',
  9   'IL',
 10   '57307',
 11   '7896');

1 row created. 

SQL> select author_key, author_last_name from author;

AUTHOR_KEY  AUTHOR_LAST_NAME
----------- ----------------------------------------
A101        jones
A102        hester
A103        weaton
A104        jeckle
A105        withers
A106        petty
A107        clark
A108        mee
A109        shagger
A110        smith
A114        thumbmasher 

11 rows selected.

Remember that we created pubs2 to increment by two.  Once you learn PL/SQL , you will be able to create a trigger that will automatically create the key on INSERT.

From the simple sequence, we move to one of the most complicated object in the database, the index.


The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.