Materialized View

View All Scripts Login to Run Script

Statement 1

Lets create a table with some data.

CREATE table my_employees 
   AS SELECT * from hr.employees

Table created.

Statement 2

Before we create a materialized view log we will need to have a primary key.

alter table my_employees ADD CONSTRAINT my_employees_pk PRIMARY KEY (employee_id)

Table altered.

Statement 3

create a materialized view log

CREATE MATERIALIZED VIEW LOG ON my_employees

Statement processed.

Statement 4

Now we can create a materialized view.

CREATE MATERIALIZED VIEW employees_mv 
   BUILD IMMEDIATE 
   REFRESH FAST ON COMMIT 
   AS SELECT * from my_employees

Statement processed.

Statement 6

Lets verify that the salary column for the selected employee is not changed, as the change is not yet committed.

select 
    EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    SALARY
from MY_EMPLOYEES
where email = 'SKING'

 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL SALARY
100 Steven King SKING 1

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	SALARY
100	Steven	King	SKING	1

Statement 7

Lets query the materialized view.

select 
    EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    SALARY
from EMPLOYEES_MV
where email = 'SKING'

 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL SALARY
100 Steven King SKING 24000

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	SALARY
100	Steven	King	SKING	24000

Statement 9

No that the changed row in the master table is committed we can see that the materialized view is also updated.

select 
    EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    SALARY
from EMPLOYEES_MV
where email = 'SKING'

 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL SALARY
100 Steven King SKING 1

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	SALARY
100	Steven	King	SKING	1

Additional Information