INCREMENT BY

The INCREMENT BY clause in an Oracle sequence specifies the value by which the sequence will be incremented (or decremented) each time the NEXTVAL function is called. This clause is crucial in determining how fast the sequence grows or shrinks.

Let’s take a deep dive into how the INCREMENT BY clause works and its practical use cases.

What is INCREMENT BY?

The INCREMENT BY clause defines the step size between consecutive numbers generated by the sequence. You can use this to control whether the sequence numbers increase or decrease by a fixed amount with each call to NEXTVAL.

• Positive Increment: If you set a positive value for INCREMENT BY, the sequence will generate numbers in ascending order.
• Negative Increment: If you set a negative value for INCREMENT BY, the sequence will generate numbers in descending order.

Syntax for INCREMENT BY

The syntax for creating a sequence with INCREMENT BY is as follows:

CREATE SEQUENCE sequence_name

  START WITH starting_value

  INCREMENT BY increment_value

  [MINVALUE min_value]

  [MAXVALUE max_value]

  [CYCLE | NOCYCLE];

For example:

CREATE SEQUENCE order_seq

  START WITH 1000

  INCREMENT BY 5;

In this case:

• The sequence starts at 1000.
• The sequence will generate the next value by adding 5 each time NEXTVAL is called, generating numbers like 1000, 1005, 1010, 1015, and so on.

Key Points About INCREMENT BY

1. Specifying a Positive Increment:
• The default behavior is an ascending sequence where each new value is greater than the previous one. For example:

CREATE SEQUENCE seq_example

  START WITH 1

  INCREMENT BY 2;

The sequence will generate: 1, 3, 5, 7, 9, 11, ...

2. Specifying a Negative Increment:

You can create a descending sequence by specifying a negative value for INCREMENT BY. For example:

CREATE SEQUENCE seq_example

  START WITH 100

  INCREMENT BY -10;

The sequence will generate: 100, 90, 80, 70, 60, 50, ...

3. Control of Sequence Growth:

INCREMENT BY controls how much the sequence value grows or shrinks each time. It provides flexibility in generating a series of numbers based on your specific requirements.

For example, to create an invoice number sequence that increments by 10, use:

CREATE SEQUENCE invoice_seq

  START WITH 1001

  INCREMENT BY 10;

4. Default Increment:
• If INCREMENT BY is not specified, the default value is 1. This means the sequence will generate numbers that increment by 1 (i.e., 1, 2, 3, 4, ...).
5. Effect of INCREMENT BY with CYCLE:
• If a sequence is created with the CYCLE option, it will start from the START WITH value and continue until it reaches the MAXVALUE or MINVALUE. Once that limit is reached, it will restart from the START WITH value (if the sequence is ascending) or from the MAXVALUE/MINVALUE (if the sequence is descending).
• Example with CYCLE:

CREATE SEQUENCE seq_example

  START WITH 1

  INCREMENT BY 1

  MAXVALUE 10

  CYCLE;

This will generate values from 1 to 10, and then restart from 1 after reaching 10.

6. Effect of INCREMENT BY with NOCYCLE:
• If NOCYCLE is specified, the sequence will stop generating new numbers once it reaches the MAXVALUE or MINVALUE. The sequence will not restart after it reaches its limit.
• Example with NOCYCLE:

CREATE SEQUENCE seq_example

  START WITH 1

  INCREMENT BY 2

  MAXVALUE 10

  NOCYCLE;

This will generate values like 1, 3, 5, 7, 9, and then stop when it tries to generate 11, as it has reached MAXVALUE.

7. Range of Increment:
• You can use both positive and negative increments, but they must align with the sequence's direction:
• An ascending sequence with a positive INCREMENT BY can have a maximum increment value that is positive and fits within the allowable data type (e.g., NUMBER in Oracle).
• A descending sequence with a negative INCREMENT BY has a maximum allowable negative range based on the data type.
8. Integer Overflow Considerations:
• If you set a very large INCREMENT BY value, be aware of potential integer overflow issues, especially if you're working with a large sequence and have a smaller data type for the sequence. Ensure that the increment is reasonable to avoid exceeding the datatype’s maximum or minimum values.

Example Scenarios for INCREMENT BY

1. Generating Unique User IDs

For creating user IDs in increments of 5:

CREATE SEQUENCE user_id_seq

  START WITH 1000

  INCREMENT BY 5;

The sequence will generate IDs like 1000, 1005, 1010, 1015, 1020, and so on.

2. Creating Invoice Numbers

If you want your invoice numbers to increment by 10:

CREATE SEQUENCE invoice_seq

  START WITH 1001

  INCREMENT BY 10;

The sequence will generate invoice numbers like 1001, 1011, 1021, 1031, 1041.

3. Descending Sequences for Decrementing Stock Levels

If you want a descending sequence to represent decreasing stock levels:

CREATE SEQUENCE stock_seq

  START WITH 100

  INCREMENT BY -1;

The sequence will generate values like 100, 99, 98, 97, and so on.

4. Sequence with CYCLE for Limited Range

A sequence that restarts after reaching a limit:

CREATE SEQUENCE num_seq

  START WITH 1

  INCREMENT BY 1

  MAXVALUE 10

  CYCLE;

This sequence will generate values from 1 to 10, and then cycle back to 1 after reaching 10.

Modifying INCREMENT BY for an Existing Sequence

If you want to change the INCREMENT BY value of an existing sequence, you cannot directly alter it. You would need to:

1. Drop the existing sequence.
2. Recreate the sequence with the new INCREMENT BY value.

DROP SEQUENCE seq_example;

 

CREATE SEQUENCE seq_example

  START WITH 1

  INCREMENT BY 2;

Conclusion

The INCREMENT BY clause in an Oracle sequence is a powerful feature that controls how sequence values are generated. It allows you to define step sizes, whether the sequence is ascending or descending, and plays a crucial role in generating unique IDs, invoice numbers, and other sequential data. It provides flexibility for fine-tuning the sequence generation process to match your business needs.