Oracle SQL Sequences, Synonyms and Indexes

Oracle SQL Sequences, Synonyms and Indexes: Complete Guide

Sequences, synonyms, and indexes are three essential schema objects in Oracle. Sequences generate unique numbers, synonyms provide alternative names for objects, and indexes dramatically improve query performance. Understanding how to create, use, and manage them is a core skill for any Oracle developer or DBA.

Learning Objectives
By the end of this guide you will master creating and using sequences (including NEXTVAL and CURRVAL), understand public vs private synonyms, know when and how to create indexes, and recognize common behaviors and limitations of each object.

1. Sequences — Generating Unique Numbers

A sequence is a database object that produces a series of unique integers. It is independent of any table and can be used across multiple tables or applications.

-- Basic sequence
CREATE SEQUENCE emp_seq
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999
  NOCYCLE
  NOCACHE;

-- Sequence with caching for better performance
CREATE SEQUENCE order_seq
  START WITH 100
  INCREMENT BY 10
  MAXVALUE 9999999
  CYCLE
  CACHE 20;

Option	Default	Purpose
START WITH	1	First value generated
INCREMENT BY	1	Step between values (can be negative)
CACHE / NOCACHE	CACHE 20	Pre-allocates values in memory for performance
CYCLE / NOCYCLE	NOCYCLE	Whether to restart after reaching MAXVALUE

2. NEXTVAL and CURRVAL

Sequences are used through two pseudocolumns:

-- Generate next value
SELECT emp_seq.NEXTVAL FROM dual;

-- Use in INSERT
INSERT INTO employees (employee_id, last_name, hire_date, job_id)
VALUES (emp_seq.NEXTVAL, 'Torres', SYSDATE, 'SA_REP');

-- Get the most recent value generated in this session
SELECT emp_seq.CURRVAL FROM dual;

Important rule: You must call NEXTVAL at least once in a session before you can use CURRVAL. Otherwise Oracle raises ORA-08002.

3. Sequence Gaps — Why They Happen

Sequences are designed for uniqueness, not for gap-free numbering. Gaps are normal and expected.

Scenario	Causes gap?	Reason
ROLLBACK after NEXTVAL	Yes	Sequence values are consumed even if the transaction is rolled back
Instance crash with CACHE enabled	Yes	Unused cached values are lost
Multiple sessions using the same sequence	Yes	Values are interleaved across sessions

Key point: Calling NEXTVAL permanently consumes the value — even if the INSERT or transaction is later rolled back.

4. Altering and Dropping Sequences

-- Modify sequence properties
ALTER SEQUENCE emp_seq
  INCREMENT BY 2
  CACHE 50;

-- Cannot change START WITH — drop and recreate instead
DROP SEQUENCE emp_seq;

CREATE SEQUENCE emp_seq START WITH 500 INCREMENT BY 1;

5. Synonyms — Alternative Names for Objects

A synonym is simply an alias for another database object (table, view, sequence, etc.). It simplifies queries and hides schema names.

-- Private synonym (visible only to you)
CREATE SYNONYM emp FOR employees;

-- Synonym for object in another schema
CREATE SYNONYM dept FOR hr.departments;

-- Drop a synonym
DROP SYNONYM emp;

6. Public vs Private Synonyms

Type	Creation syntax	Visibility
Private	CREATE SYNONYM	Only the owner (or users granted access)
Public	CREATE PUBLIC SYNONYM	All users in the database

Name resolution order: Oracle first checks your own objects, then your private synonyms, then public synonyms. A private synonym always takes precedence over a public one with the same name.

7. Indexes — Improving Query Performance

An index is a separate structure that helps Oracle find rows faster. Oracle automatically creates unique indexes for PRIMARY KEY and UNIQUE constraints.

-- Create a non-unique index
CREATE INDEX emp_last_name_idx ON employees(last_name);

-- Create a composite index
CREATE INDEX emp_name_dept_idx ON employees(last_name, department_id);

-- Drop an index
DROP INDEX emp_last_name_idx;

Cannot drop constraint-backed indexes directly: If an index was created automatically by a PRIMARY KEY or UNIQUE constraint, you must drop or disable the constraint instead of using DROP INDEX.

8. Interactive Practice Quiz

Q1. You call emp_seq.NEXTVAL in an INSERT and then immediately ROLLBACK. What is the next value returned by emp_seq.NEXTVAL?

A. The same value — it is restored on rollback B. The next value in the sequence — the rolled-back value is permanently consumed C. NULL — sequence is reset D. An error

Q2. You have a private synonym named employees and a public synonym with the same name. Which one does Oracle use when you query employees?

A. The public synonym B. The private synonym (private always takes precedence) C. Oracle raises an error D. The most recently created synonym

Q3. A table has a PRIMARY KEY on employee_id. You try to run DROP INDEX on the automatically created index. What happens?

A. The index is dropped successfully B. Oracle raises an error — you cannot drop an index supporting a constraint directly C. The index and the PRIMARY KEY are both dropped D. The index is dropped but the constraint remains

Conclusion

Sequences, synonyms, and indexes are powerful tools that help you generate unique values, simplify object references, and dramatically improve query performance. Understanding their behaviors, limitations, and best practices will make you much more effective when working with Oracle databases.

Practice creating and using these objects regularly — they appear frequently in real-world Oracle development and administration tasks.