VIEWS

View: Logically represents subsets of data from one or more tables, To reduce redundancy, data security.

Types of Views:

1. Simple Views: It is a sub query that retrieves data from one table. We can apply DML statements for these views.

SQL> create view student_v as select rollno,name from student; // view created

SQL> create view student_new_v as select rollno num,name studentname from student; //creating view from table student with other names

SQL> insert into student_v values(5,’has’); // value updated in table from view

2. Complex Views: It is a sub query that performs some actions like retrieving data from multiple tables and performing function call.

SQL> create view stu_course as select s.rollno,c.coursename from student s, course c; // creating view from multiple tables

3. Materialized views: Materialized view store a local copy of the base table records where as normal tables fetch records from the base tables;

When to use materialized views:

- When base tables have lakhs of records and summaries are executed against al or - large number of records of base table quite frequently.

- The data does not change frequently. The volume of insert, update and delete operations is considerably low.

- Any changes in the base tables needed not be reflected immediately in view. There can be some minimum allowed time delay between the changes in base tables to be reflected in materialized view data.

SQL> create materialized view mv_emp as select rollno,name from student; // materialized view created

SQL> execute DBMS_MVIEW.REFRESH (‘mv_emp’,’c’); // refreshing the materialized view

NOTE: Materialized view can also be automatically refreshed by using the START WITh <date> NEXT <date>.

SQL> create materialized view mvw_emp refresh complete start with sysdate next sysdate+1 as select rollno,name,age from student;

// view will be created and refreshed right now (sysdate) and there after once every day at the same time (sysdate+1).

SQL> drop materialized view mv_emp; // materialized view dropped SQL> drop view mv_emp; // view dropped

Data Dictionary:

SQL> select * from user_views; // shows all user created views;