Other Schema Objects: Sequences, Synonyms, and Indexes
Other Schema Objects: Sequences, Synonyms, and Indexes
This domain tests three supporting objects: sequences that generate numbers, synonyms that provide alternative names, and indexes that speed access.
Core rules
- CREATE SEQUENCE defines START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE/NOCYCLE, and CACHE/NOCACHE. NEXTVAL returns the next value; CURRVAL returns the current value for the session.
- CURRVAL is valid only after NEXTVAL in the same session. Sequence values are independent of transactions and can have gaps after rollback or cache loss.
- A synonym is an alias for a schema object. A private synonym belongs to one schema; a public synonym is available to all users and requires appropriate privileges.
- An index is a separate structure keyed by one or more columns. B-tree indexes suit selective equality and range queries; bitmap indexes suit low-cardinality columns.
- A function-based index indexes an expression, such as UPPER(last_name). A unique index enforces uniqueness.
- Indexes are maintained automatically for primary and unique constraints.
Worked example
CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 1 NOCACHE; INSERT INTO employees (employee_id, last_name) VALUES (emp_seq.NEXTVAL, 'Lee'); SELECT emp_seq.CURRVAL FROM dual; The insert consumes 100; CURRVAL returns 100 for that session. Another session cannot use this sequence’s CURRVAL until it calls NEXTVAL.
Traps
- Calling CURRVAL before NEXTVAL.
- Assuming sequence numbers are gapless or tied to commit/rollback.
- Using NEXTVAL in a WHERE clause, CHECK constraint, or view.
- Expecting a normal index to help with UPPER(column) or a leading-wildcard LIKE.