Creating and Managing Other Schema Objects (Views, Sequences, Indexes, Synonyms)
Creating and Managing Other Schema Objects (Views, Sequences, Indexes, Synonyms)
This domain tests supporting schema objects. You must know when views are updatable, how sequences generate values, how indexes affect access, and how synonyms provide names for objects.
Core rules
- A view is a stored query. A simple view based on one table without group functions, DISTINCT, or set operators is generally updatable. Complex views often are not. WITH CHECK OPTION prevents changes that would make rows invisible through the view. WITH READ ONLY blocks DML.
- Sequences generate numbers with NEXTVAL and CURRVAL. CURRVAL is available only after NEXTVAL in the same session. Caching improves performance but can create gaps after instance failure; rollback also does not restore sequence values.
- Indexes speed access but consume space and add maintenance cost. B-tree indexes suit selective queries; bitmap indexes suit low-cardinality columns in appropriate workloads. Function-based indexes require queries to use the same expression.
- Synonyms are alternative names. A private synonym belongs to one schema; a public synonym is available to all users with appropriate privileges. Synonyms can reference objects through database links.
Worked example
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1 NOCACHE; CREATE OR REPLACE VIEW open_orders AS SELECT order_id, customer_id, amount FROM orders WHERE status = 'OPEN' WITH CHECK OPTION;
order_seq.NEXTVAL returns 1000, then 1001, and so on. The view exposes only open orders. WITH CHECK OPTION rejects an insert or update that would create a row with a status other than OPEN, because that row would not appear through the view.
Traps
A view with a join, GROUP BY, or DISTINCT may reject DML. CURRVAL before NEXTVAL in a session raises an error, and sequence gaps are normal. A function-based index is not used if the query uses a different expression. Public synonyms can create name-resolution surprises. Dropping a synonym does not drop the underlying object.