1Z0-071 Exam Prep
Chapter 13
Volume 3
Sequences, synonyms, and indexes are three separate schema objects that the 1Z0-071 exam groups together. Each one has its own creation syntax, its own behaviour rules, and its own set of exam traps. The questions on sequences are the most detailed — particularly around NEXTVAL and CURRVAL and when gaps can occur.
This post covers all three objects with the exact rules the exam tests.
In this post
- Sequences — generating unique numbers
- NEXTVAL and CURRVAL — how to use a sequence
- Sequence gaps — when and why they happen
- ALTER SEQUENCE and DROP SEQUENCE
- Synonyms — alternative names for objects
- Public vs private synonyms
- Indexes — how Oracle uses them
- Creating and dropping indexes
- When indexes are and are not used
- Quick reference
- Practice questions
01 — Sequences — generating unique numbers
A sequence is a database object that generates a series of unique integers. It is independent of any table — you create it once and reference it from any DML statement that needs a unique number.
-- Basic sequence starting at 1, incrementing by 1 CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 MAXVALUE 999999 NOCYCLE NOCACHE; -- Sequence with caching for performance CREATE SEQUENCE order_seq START WITH 100 INCREMENT BY 10 MAXVALUE 9999999 CYCLE CACHE 20;
| Option | Default | What it controls |
|---|---|---|
START WITH | 1 | First value generated |
INCREMENT BY | 1 | Step between each value — can be negative for descending |
MAXVALUE | 10^27 | Highest value the sequence will generate |
MINVALUE | 1 | Lowest value (relevant for descending sequences) |
CYCLE / NOCYCLE | NOCYCLE | Whether to restart after reaching MAXVALUE |
CACHE / NOCACHE | CACHE 20 | How many values Oracle pre-allocates in memory |
02 — NEXTVAL and CURRVAL — how to use a sequence
Sequences expose two pseudocolumns: NEXTVAL generates and returns the next value, CURRVAL returns the most recently generated value for the current session.
-- Generate the next value SELECT emp_seq.NEXTVAL FROM dual; -- Returns: 1 (first call), then 2, then 3 etc. -- Use NEXTVAL in an INSERT INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) VALUES (emp_seq.NEXTVAL, 'Torres', 'TORRES', SYSDATE, 'SA_REP'); -- CURRVAL returns the last value generated in this session SELECT emp_seq.CURRVAL FROM dual; -- Returns the same value as the last NEXTVAL call in this session
CURRVAL before NEXTVAL raises an error: You cannot reference CURRVAL in a session that has not yet called NEXTVAL for that sequence. Oracle raises ORA-08002 because there is no current value to return. NEXTVAL must be called at least once in the session first.
| Pseudocolumn | What it does | Where it can be used |
|---|---|---|
seq.NEXTVAL | Generates and returns the next sequence value | SELECT (with FROM dual), INSERT VALUES, UPDATE SET |
seq.CURRVAL | Returns the last NEXTVAL for this session | Same as NEXTVAL — but NEXTVAL must be called first |
| Cannot be used in: WHERE clause, GROUP BY, HAVING, ORDER BY, DEFAULT column value, subquery in SELECT list of a view, DISTINCT queries | ||
03 — Sequence gaps — when and why they happen
Sequences do not guarantee gap-free numbers. The exam tests specifically which scenarios cause gaps. Gaps are normal and expected — sequences are designed for uniqueness, not continuity.
| Scenario | Causes a gap? | Reason |
|---|---|---|
| ROLLBACK after NEXTVAL was called | Yes | The sequence value is consumed even when the transaction is rolled back |
| System crash with CACHE enabled | Yes | Cached values that were not used are lost on restart |
| Multiple sessions using the same sequence | Yes — interleaved values | Each session gets its own next value — inserts may not be in order |
| ALTER SEQUENCE changes INCREMENT BY | Yes | The step size changes mid-stream |
| NOCACHE setting | No cache gaps — but still rollback gaps | NOCACHE prevents cache-related gaps only |
ROLLBACK does not restore sequence values: This is the most tested gap scenario. If you call NEXTVAL and then ROLLBACK the transaction, the sequence value is permanently consumed. The next NEXTVAL call returns the next number, not the rolled-back one.
04 — ALTER SEQUENCE and DROP SEQUENCE
You can modify most sequence parameters after creation. The one you cannot change is START WITH — to reset a sequence to a different starting point you must drop and recreate it.
-- Modify increment and cache size ALTER SEQUENCE emp_seq INCREMENT BY 2 CACHE 50 NOCYCLE; -- Cannot alter START WITH — drop and recreate instead DROP SEQUENCE emp_seq; CREATE SEQUENCE emp_seq START WITH 500 INCREMENT BY 1;
What can be altered: INCREMENT BY, MAXVALUE, MINVALUE, CYCLE/NOCYCLE, CACHE/NOCACHE. What cannot: START WITH. The exam tests this limitation directly.
05 — Synonyms — alternative names for objects
A synonym is an alias for another database object — a table, view, sequence, procedure, or another synonym. It lets you reference an object by a different name, which is useful for hiding the real owner name or simplifying access to objects in other schemas.
-- Private synonym — visible only to the creating user CREATE SYNONYM emp FOR employees; -- Now you can query: SELECT * FROM emp; -- Instead of: SELECT * FROM employees; -- Synonym for a table in another schema CREATE SYNONYM dept FOR hr.departments; -- Hides the schema prefix — query just: SELECT * FROM dept; -- Drop a synonym DROP SYNONYM emp; -- Removes the synonym only — underlying table is not affected
Synonyms store no data and no logic: A synonym is purely a name pointer. Dropping a synonym does not affect the underlying object. Dropping the underlying object does not automatically drop the synonym — the synonym becomes invalid but remains in the data dictionary.
06 — Public vs private synonyms
A private synonym belongs to the user who created it and is only accessible to that user (or users granted access). A public synonym is accessible to all database users without any schema prefix.
-- Public synonym — accessible to all users -- Requires CREATE PUBLIC SYNONYM privilege CREATE PUBLIC SYNONYM employees FOR hr.employees; -- Any user can now query: SELECT * FROM employees; -- Without needing to know or type the HR schema prefix -- Drop a public synonym DROP PUBLIC SYNONYM employees;
| Private synonym | Public synonym | |
|---|---|---|
| Syntax | CREATE SYNONYM | CREATE PUBLIC SYNONYM |
| Accessible by | Creating user only (or those granted access) | All database users |
| Privilege required | CREATE SYNONYM | CREATE PUBLIC SYNONYM |
| Resolution priority | Takes precedence over public synonym of same name | Used when no private synonym or table exists with that name |
Name resolution order: When you reference an object name, Oracle checks in this order: (1) your own tables and views, (2) your own private synonyms, (3) public synonyms. A private synonym always beats a public synonym of the same name.
07 — Indexes — how Oracle uses them
An index is a separate data structure that Oracle maintains alongside a table to speed up row retrieval. The exam does not test index internals deeply — it tests when indexes are created automatically, when you create them manually, and the basic types.
Automatic index creation: Oracle automatically creates a unique index whenever you define a PRIMARY KEY or UNIQUE constraint on a table. You do not need to create these manually — they exist as soon as the constraint is defined.
| Index type | Created by | Enforces uniqueness? |
|---|---|---|
| Unique index (auto) | PRIMARY KEY constraint | Yes |
| Unique index (auto) | UNIQUE constraint | Yes |
| Non-unique index (manual) | CREATE INDEX statement | No |
| Unique index (manual) | CREATE UNIQUE INDEX statement | Yes |
08 — Creating and dropping indexes
You create indexes manually when queries on a non-key column are slow and you want Oracle to be able to find rows faster using that column. The index does not change how you write queries — Oracle decides whether to use it.
-- Non-unique index on a single column CREATE INDEX emp_last_name_idx ON employees (last_name); -- Unique index on a single column (manually created) CREATE UNIQUE INDEX emp_email_idx ON employees (email); -- Composite index — multiple columns CREATE INDEX emp_name_dept_idx ON employees (last_name, department_id); -- Drop an index DROP INDEX emp_last_name_idx; -- Removes the index only — table and data are unaffected -- Cannot drop an index that was created automatically by a constraint -- Must disable or drop the constraint instead
Cannot directly drop a constraint-backed index: If Oracle created an index automatically because of a PRIMARY KEY or UNIQUE constraint, you cannot drop that index with DROP INDEX. You must disable or drop the constraint itself. The exam tests this restriction.
09 — When indexes are and are not used
Oracle's query optimiser decides whether to use an index. The decision is based on many factors, but the exam focuses on the cases where an index is clearly not useful.
| Scenario | Index used? | Reason |
|---|---|---|
WHERE last_name = 'King' | Yes — likely | Equality on indexed column is a good candidate |
WHERE UPPER(last_name) = 'KING' | No | Function applied to the column — index on last_name is bypassed |
WHERE salary + 0 = 5000 | No | Expression on the indexed column prevents index use |
| Full table scan more efficient | No | If query returns most rows, a full scan is faster |
| Column has high number of NULLs | No — for IS NULL queries | Standard B-tree indexes do not index NULL values |
Function-based indexes: To index a function applied to a column — like
UPPER(last_name) — Oracle supports function-based indexes. These are mentioned in the exam context but the syntax detail is minimal. The key point is that a regular index on last_name does not help a query that uses UPPER(last_name).
10 — Quick reference
| Rule | Detail |
|---|---|
| CURRVAL before NEXTVAL | Raises ORA-08002 — NEXTVAL must be called first in the session |
| ROLLBACK does not restore sequence values | Consumed values are gone — gaps are permanent |
| Cache crash causes gaps | Pre-cached values not used are lost on instance restart |
| START WITH cannot be altered | Drop and recreate the sequence to change the starting value |
| NEXTVAL not allowed in WHERE | Also blocked in GROUP BY, HAVING, ORDER BY, and view SELECT lists |
| Synonym does not store data | It is a name alias only — drop does not affect the underlying object |
| Private synonym beats public | Name resolution: own objects → private synonyms → public synonyms |
| PRIMARY KEY / UNIQUE auto-create index | Unique index created automatically — no manual CREATE INDEX needed |
| Cannot DROP INDEX for constraint-backed index | Must drop or disable the constraint instead |
| Function on indexed column skips index | UPPER(col) bypasses a regular index on col |
11 — Practice questions
Q1 — A developer calls
emp_seq.NEXTVAL in an INSERT statement, then immediately runs ROLLBACK. What is the next value returned by emp_seq.NEXTVAL?Q2 — A table has a PRIMARY KEY on the employee_id column. A developer tries to run
DROP INDEX emp_pk_idx to remove the index Oracle created for this constraint. What happens?Q3 — A user references the name
employees in a query. The user's schema has a private synonym named employees pointing to an archive table. A public synonym named employees also exists pointing to the HR.EMPLOYEES table. Which object does Oracle use?Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 13 covers sequences, synonyms, and indexes in full — with practice sets for every rule and scenario tested on the exam. Instant PDF download.
Get the full guide — $25 →
Chapter 13 covers sequences, synonyms, and indexes in full — with practice sets for every rule and scenario tested on the exam. Instant PDF download.
No comments:
Post a Comment