Views, Materialized Views, Sequences and Synonyms

Name resolution order: own schema object → private synonym → public synonym.

17.1 Views

Updatable join views: only the key-preserved table can be modified. Check USER_UPDATABLE_COLUMNS.

INSTEAD OF triggers make complex views updatable.

Editioning views support Edition-Based Redefinition (Module 60).

JSON Relational Duality Views (23ai, Module 31).

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d ON d.department_id = e.department_id;

CREATE OR REPLACE VIEW v_dept50 AS SELECT * FROM employees WHERE department_id = 50
WITH CHECK OPTION CONSTRAINT v_dept50_ck;

CREATE OR REPLACE VIEW v_ro AS SELECT * FROM employees WITH READ ONLY;
CREATE OR REPLACE FORCE VIEW v_future AS SELECT * FROM not_yet_created;   -- created with errors

17.2 Materialized Views

Refresh types: COMPLETE, FAST (incremental via MV logs), FORCE. Modes: ON COMMIT, ON DEMAND, ON STATEMENT (18c), scheduled.

Query rewrite: the optimizer transparently uses the MV (QUERY_REWRITE_ENABLED, QUERY_REWRITE_INTEGRITY).

Real-time materialized views (12.2: ENABLE ON QUERY COMPUTATION).

Refresh groups (DBMS_REFRESH), partition change tracking (PCT).

CREATE MATERIALIZED VIEW LOG ON order_items WITH ROWID, SEQUENCE (product_id, quantity, unit_price) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_sales_by_product
BUILD IMMEDIATE
REFRESH FAST ON COMMIT          -- or ON DEMAND / START WITH ... NEXT ...
ENABLE QUERY REWRITE
AS
SELECT product_id, SUM(quantity * unit_price) total, COUNT(*) cnt, COUNT(quantity) cq, COUNT(unit_price) cu
FROM order_items GROUP BY product_id;

EXEC DBMS_MVIEW.REFRESH('MV_SALES_BY_PRODUCT', method => 'C');   -- C = complete, F = fast, ? = force
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_SALES_BY_PRODUCT');
EXEC DBMS_ADVISOR.TUNE_MVIEW(...);

17.3 Sequences

Gaps are normal (cache loss, rollbacks). In RAC, ORDER adds overhead.

CREATE SEQUENCE seq_invoice START WITH 1000 INCREMENT BY 1 CACHE 50 NOCYCLE ORDER;
SELECT seq_invoice.NEXTVAL, seq_invoice.CURRVAL FROM dual;
ALTER SEQUENCE seq_invoice INCREMENT BY 10;
ALTER SEQUENCE seq_invoice RESTART START WITH 5000;   -- 18c
CREATE SEQUENCE seq_s SCALE EXTEND;                   -- scalable sequences (18c) reduce RAC contention
CREATE SEQUENCE seq_g SESSION;                        -- session sequences for GTTs

17.4 Synonyms

Name resolution order: own schema object → private synonym → public synonym.

CREATE SYNONYM emp FOR hr.employees;                   -- private
CREATE PUBLIC SYNONYM emp FOR hr.employees;            -- public (needs a privilege)
CREATE SYNONYM remote_orders FOR orders@sales_link;

17.5 Other Schema Objects

Clusters (index and hash), dimensions (for query rewrite), directories (CREATE DIRECTORY), contexts, editions, analytic views and hierarchies (CREATE ANALYTIC VIEW), property graphs (23ai), domains (23ai).