Sayantan's Blog On Oracle

How to increase sequence value by 100 in Oracle?

Increasing sequence column values with higher sequence value:

Let us suppose we have a table column which is incrementing its value with an existing sequence. Now we want to create a gap of 100 for next column value. Current sequence is incrementing its value with 1. So without changing the sequence we can not achieve the result.

Setup for above testing:

First Method:

--How to increase sequence value by 100 in Oracle? : Setup

CREATE SEQUENCE EMP_SEQ START WITH 1 INCREMENT BY 1;

--How to increase sequence value by 100 in Oracle? : Setup

CREATE TABLE EMPLOYEES_SEQ_TEST
(EMPLOYEE_ID    NUMBER DEFAULT EMP_SEQ.NEXTVAL,
 FIRST_NAME     VARCHAR2(30),
 LAST_NAME      VARCHAR2(30),
 SALARY         NUMBER,
 DEPARTMENT_ID  NUMBER
);

Now we will insert few records in EMPLOYEES_SEQ_TEST table.

--How to increase sequence value by 100 in Oracle? : Setup

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20, 30);

9 rows inserted.

SELECT * FROM EMPLOYEES_SEQ_TEST;

Now we want to create a gap of 100 in EMPLOYEE_ID column and then it will continue to increase by 1. But current sequence setup will not allow us to crate a gap of 100. To achieve this we will do the following:

ALTER SEQUENCE EMP_SEQ INCREMENT BY 100;

SELECT EMP_SEQ.CURRVAL FROM DUAL;

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10);

SELECT * FROM EMPLOYEES_SEQ_TEST;

In the above example we can see, that a gap of 100 is created. Now we will revert the increment by 1 again.

ALTER SEQUENCE EMP_SEQ INCREMENT BY 1;

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60);

SELECT * FROM EMPLOYEES_SEQ_TEST ORDER BY EMPLOYEE_ID;

Now we can see that new EMPLOYEE_IDs are increasing with sequence value of 1.

Second Method:

Reset the first setup:

DROP SEQUENCE EMP_SEQ;

DELETE EMPLOYEES_SEQ_TEST;

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 20);

SELECT * FROM EMPLOYEES_SEQ_TEST ORDER BY EMPLOYEE_ID;

Now there is a short cut method available. We will use that method to achieve the result of 1st method.

ALTER SEQUENCE EMP_SEQ RESTART START WITH 109;

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10);

SELECT * FROM EMPLOYEES_SEQ_TEST ORDER BY EMPLOYEE_ID;

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60);

SELECT * FROM EMPLOYEES_SEQ_TEST ORDER BY EMPLOYEE_ID;

Now we can see that without re-incrementing, the EMPLOYEE_ID value is increasing with sequence value 1.

Increasing non sequence column values using sequence:

Now let us suppose the scenario where value of EMPLOYEE_ID column is increasing randomly. Now we want to use sequence for increasing the EMPLOYEE_ID value.

Initialize the setup:

DROP SEQUENCE EMP_SEQ;

DROP TABLE EMPLOYEES_SEQ_TEST;

Creating the setup:

CREATE TABLE EMPLOYEES_SEQ_TEST
(EMPLOYEE_ID    NUMBER,
 FIRST_NAME     VARCHAR2(30),
 LAST_NAME      VARCHAR2(30),
 SALARY         NUMBER,
 DEPARTMENT_ID  NUMBER
);

INSERT INTO EMPLOYEES_SEQ_TEST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 20);

SELECT * FROM EMPLOYEES_SEQ_TEST ORDER BY EMPLOYEE_ID;

In the above example, we can see that the maximum EMPLOYEE_ID is 208. Now onward, we will use the sequence EMP_SEQ to increase the EMPLOYEE_ID column.

CREATE SEQUENCE EMP_SEQ START WITH 208 INCREMENT BY 1;

ALTER TABLE EMPLOYEES_SEQ_TEST MODIFY(EMPLOYEE_ID    NUMBER DEFAULT EMP_SEQ.NEXTVAL);

INSERT INTO EMPLOYEES_SEQ_TEST(FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID)
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (60);

SELECT * FROM EMPLOYEES_SEQ_TEST ORDER BY EMPLOYEE_ID;

Now we can see that the new EMPLOYEE_IDs are using the sequence value.

RELATED TOPICS:

• Index Scanning Methods In Oracle