START WITH

The START WITH clause in an Oracle sequence specifies the first number that the sequence will generate. It is a fundamental property when creating a sequence, as it dictates where the sequence begins before subsequent increments occur.

Let’s dive deeper into the START WITH functionality and its use in Oracle sequences.

What is START WITH?

• The START WITH clause defines the initial value of the sequence.
• This value is the first number returned by the sequence when you call NEXTVAL for the first time.
• It is the starting point of the sequence and is used in conjunction with other sequence properties like INCREMENT BY, MINVALUE, MAXVALUE, and CYCLE to control how the sequence behaves.

Syntax for START WITH

When creating a sequence, the syntax for using the START WITH clause is as follows:

CREATE SEQUENCE sequence_name

  START WITH starting_value

  INCREMENT BY increment_value

  [MINVALUE min_value]

  [MAXVALUE max_value]

  [CYCLE | NOCYCLE];

Example of START WITH:

CREATE SEQUENCE emp_seq

  START WITH 100

  INCREMENT BY 1;

In this example:

• The sequence emp_seq will start at 100.
• Every time you call NEXTVAL, it will return the next sequential number, starting from 100.

Key Points About START WITH

1. Initial Value:
• The START WITH value is the first number generated by the sequence. It is not automatically incremented, so the very first call to NEXTVAL will return exactly the value specified in the START WITH clause.
2. Default Value:
• If the START WITH clause is not specified, Oracle will default to 1 for ascending sequences or -1 for descending sequences. These defaults can be overridden by explicitly specifying a different start value.
3. Influence on Sequence Generation:
• The START WITH value defines the starting point for the sequence. For example, if you specify START WITH 10 and INCREMENT BY 2, the sequence will generate numbers in the following order: 10, 12, 14, 16, ....
4. Interaction with INCREMENT BY:
• The INCREMENT BY value will determine how the sequence increments after the first value specified by START WITH. For example, if START WITH 1 and INCREMENT BY 5, the sequence will generate values like 1, 6, 11, 16, ....
5. Can Be Set to Any Number:
• The START WITH value can be any number, including negative numbers, which is useful when creating descending sequences.

Example with Negative START WITH:

CREATE SEQUENCE emp_seq

  START WITH -100

  INCREMENT BY -1;

In this example:

• The sequence emp_seq will start at -100 and decrement by 1. The next values would be -101, -102, -103, and so on.

Modifying START WITH

Once a sequence is created, you cannot modify the START WITH value directly through the ALTER SEQUENCE statement. If you need to change the starting point of a sequence, you must:

1. Drop the existing sequence.
2. Recreate it with the new START WITH value.

Example:

To modify the starting value, you can drop and recreate the sequence:

DROP SEQUENCE emp_seq;

 

CREATE SEQUENCE emp_seq

  START WITH 500

  INCREMENT BY 1;

This would change the sequence emp_seq to start from 500 rather than its previous starting value.

Behavior with CYCLE and NOCYCLE

• If a sequence is created with the CYCLE option, once it reaches its MAXVALUE, it will restart at its MINVALUE (or START WITH for ascending sequences) and begin generating values again.

For example:

CREATE SEQUENCE emp_seq

  START WITH 100

  INCREMENT BY 1

  MAXVALUE 105

  CYCLE;

This will generate values starting from 100 and loop back to 100 after reaching 105.

• If the sequence is created with the NOCYCLE option, once the sequence reaches its MAXVALUE or MINVALUE, it will stop generating new values, and subsequent calls to NEXTVAL will raise an error.

For example:

CREATE SEQUENCE emp_seq

  START WITH 100

  INCREMENT BY 1

  MAXVALUE 105

  NOCYCLE;

This will generate values starting from 100, and once it reaches 105, it will stop.

Practical Use Cases for START WITH

1. Unique ID Generation:
• A common use for START WITH is to control the starting value for unique identifiers such as employee IDs, order numbers, or invoice numbers. You might want to start at a specific number to align with business rules, such as starting from a higher number for certain regions or departments.
2. Gap-Free Number Generation:
• In cases where you need to generate numbers in a predictable, incremental manner (e.g., for numbering invoices), specifying a custom START WITH value ensures that the sequence starts exactly where you want.
3. Data Segmentation:
• If you're managing different segments of data (e.g., customer IDs for different business units), you might set the START WITH value differently for each sequence to ensure that IDs in different segments don’t overlap.
4. Descending Sequences:
• When using a descending sequence (for example, when generating reverse chronological order numbers), you can use START WITH to specify the initial highest number, and the sequence will generate decreasing values.

 

Example of Sequence with START WITH and INCREMENT BY

CREATE SEQUENCE order_seq

  START WITH 500

  INCREMENT BY 5;

• Start Value: The first value returned by NEXTVAL will be 500.
• Increment: After the first value, each subsequent value will increase by 5, so the next values will be 505, 510, 515, and so on.

Conclusion

• START WITH defines the initial value of a sequence.
• It is a critical property for controlling where a sequence starts, whether for unique ID generation, time-based values, or other types of sequential data.
• Once the sequence is created, you cannot change the START WITH value directly but can drop and recreate the sequence if needed.
• By setting a specific START WITH, you can tailor sequences to fit business logic, ensuring they start at the right point and follow the intended increment.