Creating Other Schema Objects
What the domain covers
Other schema objects support queries and data generation: views, sequences, indexes, and synonyms. This domain tests CREATE statements, options, and behavior.
Core rules
- A view is a stored query. A simple view uses one table and can support DML; a complex view with joins, group functions, or DISTINCT generally cannot. WITH READ ONLY blocks DML. WITH CHECK OPTION prevents changes that would make rows invisible to the view.
- A sequence generates numeric values. NEXTVAL returns the next value; CURRVAL returns the current value for the session after NEXTVAL has been called. Sequences can cache values for performance; gaps can occur after rollback, cache loss, or shared use.
- An index speeds access to rows by key. Oracle automatically creates indexes for PRIMARY KEY and UNIQUE constraints. You can create additional indexes on frequently filtered columns.
- A synonym is an alias for a schema object. A private synonym belongs to one user; a public synonym is available to all. A synonym does not grant privileges.
Worked example
CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10; SELECT emp_seq.NEXTVAL FROM dual;
Reasoning: the sequence starts at 100 and increases by 10. The first NEXTVAL returns 100. A later NEXTVAL in the same session returns 110. CURRVAL would return 100 only after that first NEXTVAL; calling CURRVAL before NEXTVAL raises an error. The value is generated independently of any table, so it can be used in an INSERT.
Traps
Do not call CURRVAL before NEXTVAL in a session. Sequence values are not guaranteed to be gap-free. A view with WITH CHECK OPTION rejects rows that the view could not select. An index is not automatically used for every query. Creating a synonym does not give another user access to the underlying object.