Preamble
In Oracle/PLSQL, you can create autonumbering using a sequence. A sequence is an Oracle object that is used to generate a number sequence. This can be useful when you need to create a unique number as a primary key.
CREATE SEQUENCE
Syntax
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
- sequence_name of the sequence you want to create.
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
This code will create a sequence object called supplier_seq. The first number of the sequence is 1, each subsequent number will be incremented by 1 (i.e., 2,3,4, …). This will cache up to 20 values for performance.
If you omit the MAXVALUE parameter, your default sequence will be to:
MAXVALUE 999999999999999999999999999
In this way, you can simplify the CREATE SEQUENCE. By writing the following:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Now that you have created a sequence object to auto-number the counter field, we will look at how to get a value from that sequence object. To get the next value, you need to use NEXTVAL.
For example, you can use NEXTVAL:
supplier_seq.NEXTVAL;
This will allow you to extract the next value from the supplier_seq sequence. You need to use the NEXTVAL clause in your SQL query. For example:
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');
This insert request will insert a new entry into the table (suppliers). The Supplier_id field will be assigned the following number from the supplier_seq sequence. The supplier_name field will be set to ‘Kraft Foods’.
DROP SEQUENCE
Once you have created a sequence in Oracle, you may need to remove it from the database.
Syntax
DROP SEQUENCE sequence_name;
- sequence_name is the name of the sequence you want to delete.
Let’s look at an example of how to remove a sequence in Oracle.
DROP SEQUENCE supplier_seq;
This example will remove the sequence supplier_seq.
FAQS
Q: When creating a sequence, what do the cache and nocache options mean? For example, you could create a sequence with the cache 20 option as follows:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Or you could create the same sequence, but with the nocache option:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
A:As for the sequence, the cache option determines how many sequence values will be stored in memory for quick access.
The disadvantage of creating a sequence with cache is that if a system failure occurs, all cached sequence values that were not used will be lost. This will cause gaps in the values assigned to the sequence. When the system recovers, Oracle will cache new numbers from where the sequence was interrupted, ignoring the lost sequence values.
Note: To recover lost sequence values, you can always execute the ALTER SEQUENCE command to reset the counter to the correct value. nocache means that none of the sequence values are stored in memory. This option may reduce performance, however, you should not experience any gaps in the values assigned to the sequence.
Q: How do I set the lastvalue value to an Oracle sequence?
A: You can change the lastvalue for an Oracle sequence by executing the ALTER command in the sequence.
For example, if the last value of the Oracle sequence in use was 100 and you want the next value to be 225. You must execute the following commands.
ALTER SEQUENCE seq_name
IT'S BY 124;
SELECT seq_name.nextval FROM dual;
ALTER SEQUENCE seq_name.
IT'S BY 1;
The next sequence value to use is now 225.
SQL tutorial: SEQUENCE in Oracle Database
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…