
--<<1.Complete refresh
--DROP MATERIALIZED VIEW mv_complete_refresh
CREATE MATERIALIZED VIEW mv_complete_refresh
REFRESH COMPLETE ON COMMIT
AS
SELECT emp_id, emp_name, dept_name, salary 
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

begin
 DBMS_MVIEW.REFRESH('mv_complete_refresh','C');
END;

SELECT * FROM mv_complete_refresh
--1.Complete refresh>>

--<<2.Fast Refresh
/*Unsupported Query in Materialized View

Aggregate functions without a GROUP BY
Complex joins or subqueries
DISTINCT, UNION, MINUS, INTERSECT
Non-deterministic functions (SYSDATE, USER, CURRENT_TIMESTAMP)

Ensure the query is simple and follows the rules for FAST REFRESH. Try using only a single-table query or a JOIN with primary keys.
*/

DROP MATERIALIZED VIEW LOG ON employees;
DROP MATERIALIZED VIEW LOG ON departments;

CREATE MATERIALIZED VIEW LOG ON departments
WITH PRIMARY KEY, ROWID, SEQUENCE
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY, ROWID, SEQUENCE
INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW mv_fast_refresh
CREATE MATERIALIZED VIEW mv_fast_refresh
REFRESH FAST ON COMMIT
AS
SELECT 
  e.rowid AS emp_rowid,          -- Rowid from employees table
  d.rowid AS dept_rowid,         -- Rowid from departments table
  e.emp_id,
  e.emp_name,
  e.salary,
  d.department_id,
  d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;


--Validate Materialized View (Perform Complete Refresh First)
begin
	DBMS_MVIEW.REFRESH('mv_fast_refresh', 'F');
END;--ONLY once

SELECT * FROM mv_fast_refresh
--2.Fast Refresh>>

--<<3.Force Refresh
--Uses fast refresh if possible; otherwise, does a complete refresh.
--DROP MATERIALIZED VIEW mv_force_refresh
CREATE MATERIALIZED VIEW mv_force_refresh
REFRESH FORCE ON demand
AS
SELECT emp_id, emp_name, dept_name, salary 
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

begin
	DBMS_MVIEW.REFRESH('mv_fast_refresh', 'C');
END;--ONLY once

SELECT * FROM mv_force_refresh

--3.Force Refresh>>

--<<4. aggregation MV

DROP MATERIALIZED VIEW LOG ON employees; --It will NOT WORK FOR aggregation case


CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, PRIMARY KEY, SEQUENCE (department_id, salary)
INCLUDING NEW VALUES;						--This will WORK also FOR REFRESH FAST ON COMMIT 

--You must include columns referenced in GROUP BY or in aggregates in the MV log.

CREATE MATERIALIZED VIEW mv_avg_salary
REFRESH FAST ON DEMAND
AS
SELECT 
  department_id, 
  AVG(salary) AS avg_salary, 
  COUNT(salary) AS num_employees
FROM employees
GROUP BY department_id;


BEGIN
	DBMS_MVIEW.REFRESH('mv_avg_salary', 'C');
END;

SELECT * FROM mv_avg_salary

--4. aggregation MV>>