Materialized Views 100 Queries

1-20: Basic Materialized View Creation & Query

-- 1. Create a simple materialized view

CREATE MATERIALIZED VIEW mv_emp AS

SELECT emp_id, emp_name, salary FROM employees;

 

-- 2. Create a materialized view with aggregation

CREATE MATERIALIZED VIEW mv_dept_salary AS

SELECT department_id, AVG(salary) AS avg_salary

FROM employees GROUP BY department_id;

 

-- 3. Create a materialized view with join

CREATE MATERIALIZED VIEW mv_emp_dept AS

SELECT e.emp_name, d.dept_name

FROM employees e JOIN departments d ON e.department_id = d.dept_id;

 

-- 4. Create a materialized view with WHERE clause filter

CREATE MATERIALIZED VIEW mv_high_salary AS

SELECT * FROM employees WHERE salary > 100000;

 

-- 5. Query a materialized view

SELECT * FROM mv_emp;

 

-- 6. Drop a materialized view

DROP MATERIALIZED VIEW mv_emp;

 

-- 7. Create a materialized view with fast refresh capability

CREATE MATERIALIZED VIEW mv_fast_refresh

REFRESH FAST ON DEMAND

AS SELECT emp_id, emp_name FROM employees;

 

-- 8. Create a materialized view with complete refresh only

CREATE MATERIALIZED VIEW mv_complete_refresh

REFRESH COMPLETE ON DEMAND

AS SELECT * FROM employees;

 

-- 9. Create a materialized view with partitioning

CREATE MATERIALIZED VIEW mv_partitioned_emp

PARTITION BY HASH(emp_id)

AS SELECT emp_id, emp_name FROM employees;

 

-- 10. Create a materialized view with order by (note: ordering only for query plan)

CREATE MATERIALIZED VIEW mv_ordered_emp AS

SELECT * FROM employees ORDER BY salary DESC;

 

-- 11. Create a materialized view using DISTINCT

CREATE MATERIALIZED VIEW mv_distinct_dept AS

SELECT DISTINCT department_id FROM employees;

 

-- 12. Create a materialized view with GROUP BY rollup

CREATE MATERIALIZED VIEW mv_rollup_dept

AS SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY ROLLUP(department_id, job_id);

 

-- 13. Create a materialized view with inline function call

CREATE MATERIALIZED VIEW mv_bonus AS

SELECT emp_id, emp_name, calculate_bonus(salary) AS bonus FROM employees;

 

-- 14. Create a materialized view on multiple tables with joins and filters

CREATE MATERIALIZED VIEW mv_emp_job_dept AS

SELECT e.emp_name, j.job_title, d.dept_name

FROM employees e

JOIN jobs j ON e.job_id = j.job_id

JOIN departments d ON e.department_id = d.dept_id

WHERE e.salary > 50000;

 

-- 15. Query metadata about materialized views from USER_MVIEWS

SELECT mview_name, last_refresh_date, refresh_mode FROM user_mviews;

 

-- 16. Query DBA_MVIEWS for all materialized views

SELECT owner, mview_name, refresh_mode FROM dba_mviews;

 

-- 17. Query USER_MVIEW_REFRESH_TIMES for refresh timestamps

SELECT mview_name, refresh_start_time, refresh_end_time FROM user_mview_refresh_times;

 

-- 18. Create a materialized view with query rewrite enabled

CREATE MATERIALIZED VIEW mv_query_rewrite

ENABLE QUERY REWRITE

AS SELECT emp_name, salary FROM employees WHERE salary > 50000;

 

-- 19. Enable query rewrite on existing materialized view

ALTER MATERIALIZED VIEW mv_emp ENABLE QUERY REWRITE;

 

-- 20. Disable query rewrite on materialized view

ALTER MATERIALIZED VIEW mv_emp DISABLE QUERY REWRITE;

________________________________________

21-40: Materialized View Refreshes

-- 21. Refresh a materialized view on demand (complete refresh)

EXEC DBMS_MVIEW.REFRESH('MV_EMP');

 

-- 22. Refresh a materialized view with fast refresh

EXEC DBMS_MVIEW.REFRESH('MV_FAST_REFRESH', method => 'F');

 

-- 23. Refresh materialized view with complete refresh and atomic refresh = FALSE

EXEC DBMS_MVIEW.REFRESH('MV_EMP', method => 'C', atomic_refresh => FALSE);

 

-- 24. Refresh multiple materialized views

EXEC DBMS_MVIEW.REFRESH('MV_EMP, MV_DEPT_SALARY');

 

-- 25. Schedule automatic refresh at fixed interval using DBMS_SCHEDULER

BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

    job_name => 'mv_refresh_job',

    job_type => 'PLSQL_BLOCK',

    job_action => 'BEGIN DBMS_MVIEW.REFRESH(''MV_EMP''); END;',

    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',

    enabled => TRUE

  );

END;

/

 

-- 26. Refresh materialized view with parallelism (set degree of parallelism)

EXEC DBMS_MVIEW.REFRESH('MV_EMP', parallelism => 4);

 

-- 27. Create materialized view with refresh on commit

CREATE MATERIALIZED VIEW mv_refresh_on_commit

REFRESH FAST ON COMMIT

AS SELECT * FROM employees;

 

-- 28. Create materialized view with refresh on demand

CREATE MATERIALIZED VIEW mv_refresh_on_demand

REFRESH COMPLETE ON DEMAND

AS SELECT * FROM employees;

 

-- 29. Create materialized view with START WITH and NEXT to schedule refresh (deprecated)

CREATE MATERIALIZED VIEW mv_scheduled_refresh

REFRESH COMPLETE

START WITH SYSDATE

NEXT SYSDATE + 1

AS SELECT * FROM employees;

 

-- 30. Refresh materialized view with FORCE refresh (fast if possible, else complete)

EXEC DBMS_MVIEW.REFRESH('MV_EMP', method => 'F');

 

-- 31. Create a materialized view with incremental refresh

CREATE MATERIALIZED VIEW mv_incr_refresh

REFRESH FAST ON DEMAND

AS SELECT * FROM employees;

 

-- 32. Manually refresh a materialized view log (not usually required)

-- No direct command; refresh logs automatically on DML.

 

-- 33. Set materialized view refresh group

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_EMP');

 

-- 34. Refresh materialized view in parallel with no atomic refresh

EXEC DBMS_MVIEW.REFRESH('MV_EMP', parallelism => 8, atomic_refresh => FALSE);

 

-- 35. Create a materialized view with refresh with rowid tracking

CREATE MATERIALIZED VIEW mv_rowid_refresh

REFRESH FAST ON DEMAND

AS SELECT ROWID, emp_id, emp_name FROM employees;

 

-- 36. Create a materialized view with incremental join group

CREATE MATERIALIZED VIEW mv_join_group

REFRESH FAST ON DEMAND

AS SELECT d.dept_name, COUNT(e.emp_id) AS emp_count

FROM employees e JOIN departments d ON e.department_id = d.dept_id

GROUP BY d.dept_name;

 

-- 37. Monitor materialized view refresh job status in DBA_SCHEDULER_JOB_RUN_DETAILS

SELECT job_name, status, error_message

FROM dba_scheduler_job_run_details

WHERE job_name = 'MV_REFRESH_JOB';

 

-- 38. Query USER_MVIEW_ANALYSIS for refresh capability info

SELECT mview_name, fast_refreshable FROM user_mview_analysis;

 

-- 39. Use EXPLAIN_MVIEW to check refreshability

BEGIN

  DBMS_MVIEW.EXPLAIN_MVIEW('MV_EMP');

END;

/

 

-- 40. Create a materialized view with refresh after system restart using DBMS_MVIEW.

EXEC DBMS_MVIEW.REFRESH('MV_EMP', atomic_refresh => FALSE);

________________________________________

41-60: Materialized View Logs

-- 41. Create materialized view log on table (for fast refresh)

CREATE MATERIALIZED VIEW LOG ON employees

WITH ROWID, SEQUENCE (department_id, salary);

 

-- 42. Create materialized view log with primary key option

CREATE MATERIALIZED VIEW LOG ON employees

WITH PRIMARY KEY, SEQUENCE (salary);

 

-- 43. Create materialized view log with including new values (for fast refresh with updates)

CREATE MATERIALIZED VIEW LOG ON employees

WITH ROWID, INCLUDING NEW VALUES;

 

-- 44. Drop materialized view log

DROP MATERIALIZED VIEW LOG ON employees;

 

-- 45. Query existing materialized view logs

SELECT log_table FROM dba_mview_logs WHERE master = 'EMPLOYEES';

 

-- 46. Check materialized view log columns

SELECT column_name FROM dba_tab_columns WHERE table_name = 'MLOG$_EMPLOYEES';

 

-- 47. Create materialized view log on a partitioned table

CREATE MATERIALIZED VIEW LOG ON employees PARTITION employees_2020;

 

-- 48. Create materialized view log with including columns only (no rowid)

CREATE MATERIALIZED VIEW LOG ON employees

WITH PRIMARY KEY (emp_id);

 

-- 49. Create materialized view log for join fast refresh (with rowid and sequence)

CREATE MATERIALIZED VIEW LOG ON employees

WITH ROWID, SEQUENCE, INCLUDING NEW VALUES;

 

-- 50. Create materialized view log for a table with complex datatype columns (only allowed on simple columns)

 

-- 51. Query MVIEW$ and MVIEW_DETAIL_RELATIONS for log details (DBA views)

 

-- 52. Identify missing materialized view logs preventing fast refresh

 

-- 53. Use DBMS_MVIEW.PURGE_MVIEW_LOGS to clean logs

 

-- 54. Check last refresh type on materialized views (using DBA_MVIEWS)

 

-- 55. Rebuild materialized view logs if corrupted (by drop and recreate)

 

-- 56. Materialized view logs are automatically maintained by Oracle upon table DML

 

-- 57. Create materialized view log with logging disabled (NOT recommended)

 

-- 58. Use Oracle Enterprise Manager to monitor materialized view logs and refreshes

 

-- 59. Use ANALYZE TABLE on materialized view logs for statistics

 

-- 60. Ensure materialized view logs have relevant columns used in materialized view

________________________________________

61-80: Materialized View Security & Grants

-- 61. Grant SELECT on materialized view to another user

GRANT SELECT ON mv_emp TO hr_user;

 

-- 62. Revoke SELECT on materialized view from a user

REVOKE SELECT ON mv_emp FROM hr_user;

 

-- 63. Grant CREATE MATERIALIZED VIEW privilege to a user

GRANT CREATE MATERIALIZED VIEW TO hr_user;

 

-- 64. Grant REFRESH on materialized view (indirect - handled via roles)

 

-- 65. Secure a materialized view using VPD policies (Virtual Private Database)

 

-- 66. Use roles to restrict access to materialized views

 

-- 67. Create synonyms for materialized views for easier access

CREATE SYNONYM emp_mv FOR mv_emp;

 

-- 68. Use materialized views in roles to control access

 

-- 69. Encrypt materialized views data via Transparent Data Encryption (TDE)

 

-- 70. Audit materialized view access using Oracle Audit features

 

-- 71. Create read-only materialized views to prevent DML

 

-- 72. Use resource manager to limit CPU usage on materialized view refresh jobs

 

-- 73. Use Oracle Label Security on tables underlying materialized views

 

-- 74. Implement fine-grained auditing on materialized view usage

 

-- 75. Manage materialized view logs permissions to control who can refresh

 

-- 76. Use proxy users to restrict refresh execution

 

-- 77. Monitor materialized view usage via Oracle Enterprise Manager

 

-- 78. Create materialized view with encryption on sensitive columns

 

-- 79. Manage grants for DBMS_MVIEW package

 

-- 80. Use Oracle Database Vault to protect materialized view data

________________________________________

81-100: Maintenance, Performance & Miscellaneous

-- 81. Analyze materialized view to gather optimizer statistics

ANALYZE TABLE mv_emp COMPUTE STATISTICS;

 

-- 82. Gather statistics using DBMS_STATS

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'MV_EMP');

 

-- 83. Rebuild materialized view for performance optimization (drop & recreate)

 

-- 84. Monitor materialized view size with DBA_SEGMENTS

SELECT segment_name, bytes/1024/1024 AS size_mb FROM dba_segments WHERE segment_name = 'MV_EMP';

 

-- 85. Use EXPLAIN PLAN to analyze materialized view query plan

EXPLAIN PLAN FOR SELECT * FROM mv_emp;

 

-- 86. Create a materialized view with parallel query option

CREATE MATERIALIZED VIEW mv_parallel

PARALLEL 4

AS SELECT * FROM employees;

 

-- 87. Refresh materialized view with NO LOGGING option for faster refresh

 

-- 88. Use DBMS_MVIEW.EXPLAIN_MVIEW to diagnose refreshability issues

 

-- 89. Disable logging on base table to speed up materialized view refresh (use carefully)

 

-- 90. Use partition change tracking (PCT) for fast refresh on partitioned tables

 

-- 91. Schedule materialized view refresh during off-peak hours

 

-- 92. Use materialized view rewrite with cost-based optimizer

 

-- 93. Use Oracle Resource Manager to prioritize refresh jobs

 

-- 94. Compress materialized views to save space

CREATE MATERIALIZED VIEW mv_compress COMPRESS FOR OLTP AS SELECT * FROM employees;

 

-- 95. Use materialized

view rewrite on summary tables

-- 96. Use flashback queries with materialized views for point-in-time refresh

-- 97. Drop materialized view if exists (PL/SQL block)

DECLARE

v_count INTEGER;

BEGIN

SELECT COUNT(*) INTO v_count FROM user_mviews WHERE mview_name = 'MV_EMP';

IF v_count > 0 THEN

EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW MV_EMP';

END IF;

END;

/

-- 98. Monitor invalid materialized views in USER_OBJECTS

SELECT object_name, status FROM user_objects WHERE object_type = 'MATERIALIZED VIEW' AND status = 'INVALID';

-- 99. Create materialized view with query rewrite hint

CREATE MATERIALIZED VIEW mv_hint

ENABLE QUERY REWRITE

AS SELECT /*+ MATERIALIZE */ emp_id, emp_name FROM employees;

-- 100. Use DBMS_MVIEW.EXPLAIN_MVIEW to troubleshoot fast refresh errors

BEGIN

DBMS_MVIEW.EXPLAIN_MVIEW('MV_EMP');

END;

/