Views, Sequences, Synonyms and Indexes
Domain 10 tests schema objects that simplify access, generate numbers, and improve performance. You must know view restrictions, sequence behavior, synonym resolution, and index trade-offs.
Core rules
- A view is a stored query. A simple view uses one table and can allow DML if it contains no restrictions such as group functions or DISTINCT. A complex view often requires an inline view or INSTEAD OF trigger for DML.
- CREATE OR REPLACE VIEW changes a view without dropping it. WITH CHECK OPTION prevents changes that would make rows invisible through the view. WITH READ ONLY blocks DML through the view.
- A sequence generates numbers with NEXTVAL and CURRVAL. NEXTVAL advances the sequence; CURRVAL returns the current value for the session after NEXTVAL has been called. Sequence numbers can have gaps.
- A synonym is an alias for a table, view, sequence, or other object. A public synonym is available to all users; a private synonym belongs to one schema.
- An index speeds retrieval for some queries but adds overhead to DML. B-tree indexes suit selective equality and range predicates; function-based indexes support expressions.
Worked example
CREATE VIEW high_paid AS SELECT employee_id, salary FROM employees WHERE salary > 10000 WITH CHECK OPTION;
This view exposes only salaries above 10000. WITH CHECK OPTION prevents an INSERT or UPDATE through the view that would create a row with salary at or below 10000, because that row would not be visible through the view. The base table can still be changed directly.
Traps
CURRVAL cannot be used before NEXTVAL in the same session. Sequences do not guarantee gap-free numbers. A view with GROUP BY is not automatically updatable. An index is not guaranteed to be used and can slow inserts. Dropping an object can invalidate dependent views.
Key points
- WITH CHECK OPTION enforces view visibility.
- WITH READ ONLY blocks DML through the view.
- NEXTVAL advances; CURRVAL needs a prior NEXTVAL.
- Sequence numbers can have gaps.
- Indexes speed reads but add DML overhead.