1Z0-149: Managing Indexes, Synonyms, and Sequences

What this domain tests

This domain tests index, synonym, and sequence objects: creating them, using them, and understanding their behavior.

Core rules

• CREATE INDEX builds an index. The default is a B-tree index. A composite index lists multiple columns; the leading column matters for simple predicates.
• A unique index enforces uniqueness. Primary key and unique constraints create indexes automatically unless an existing index is supplied.
• Function-based indexes index an expression, such as UPPER(last_name), and can support queries using that expression.
• A synonym is an alias for a schema object. A private synonym is visible to its owner; a public synonym is visible to all users with object privileges.
• CREATE SEQUENCE defines a numeric generator. NEXTVAL returns the next value; CURRVAL returns the last value obtained in the current session.
• Sequences can cache values and can cycle. Gaps are normal after rollbacks, cache loss, or concurrent sessions.

Worked example

CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10 NOCACHE; INSERT INTO employees (employee_id, last_name) VALUES (emp_seq.NEXTVAL, 'Smith');

The insert calls NEXTVAL, which returns 100 and advances the sequence to 110. A second call in the same session returns 110. CURRVAL would return 100 before the second call. If the transaction rolls back, the sequence does not return to 100.

Traps

• Calling CURRVAL before NEXTVAL in a session.
• Assuming an index guarantees row order in a query; only ORDER BY does.
• Applying a function to an indexed column and expecting a normal index to be used.
• Expecting a sequence to be transaction-safe or gap-free.
• Creating a public synonym without the required privilege.