View and Materialized view

1. View and Materialized view

2. What is a view? • Logically represents subset of data from one or more table. • In sql, a view is a virtual relation based on result-set of a SELECT statement. • It contains rows and columns just like a real table. • Views contains no data of its own but is like a window through which data from table can be viewed or changed. • The table on which view a view is based are called BASE TABLE. • View is stored as a SELECT statement in the data dictonary. • SYNTAX CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;

3. Advantages of views • Views restrict access to data. • Views can be used to make simple queries to retrieve the result of complicated queries. • Views provide group of users access to data according to their particular criteria.

4. Types of views • Simple views • Complex views

5. Difference between simple and complex views Simple views Complex views • A simple view is one that : • Derives data from one table. • Contains no functions or group of data. • Can perform DML operations through view. Example: CREATE VIEW emp_view AS SELECT f_name, salary from Employees WHERE dep_id=90; • A complex view is one that: • Derives data from many tables. • Contains functions or group of data. • Does not always allow DML operations through view. • In particular, complex views can contain: -join condition -Distinct -Pseudo columns -Group By clause -Having clause Example: CREATE VIEW comp_view AS SELECT e.f_name,e.emp_id,e.job_id,e.dep_id,d.dep_id,d.location WHERE e.dep_id=d.dep_id;

6. Creating a view • You can create a view by embedding a subquery within the CREATE VIEW statement. • The sub query that defines the view can not contain ORDER BY clause. The ORDER BY clause is specified when you retrieve data from the view. • Syntax: CREATE OR REPLACE view view_name AS Subquery; In the syntax: OR REPLACE – re_creates the view if its already exists. View _name – name of the view. Subquery - SELECT statement. Eg: CREATE OR REPLACE VIEW emp_view AS SELECT f_name, salary from Employees WHERE dep_id=90; SELECT * FROM emp_view;

7. View WITH READ ONLY option • To restrict the data manipulation using views is done using WITH READ ONLY. CREATE OR REPLACE view v1 AS SELECT A from T1 Where A=1 WITH READ ONLY; • Now you perform a DML operation on this view it will throw an error. Eg: INSERT into v1 value(3); Output: ORA-01733: virtual column not allowed here

8. View WITH CHECK OPTION • It specifies that only rows accessible to the view can be inserted or updated. • Here WHERE clause will be checked. • If WHERE clause does not satisfy error will be thrown Eg: CREATE OR REPLACE view v1 AS SELECT A FROM T1 WHERE A=1 WITH CHECK OPTION; INSERT into v1 values(3); Output: ORA-01402: view WITH CHECK OPTION where-clause violation

9. View with FORCE option • Creates the view regardless of whether or not the base table exist. • But we will get error. Eg : CREATE OR REPLACE FORCE view v_new AS SELECT * FROM GREENS; • When we execute this particular query view will be created but with error. Output: ORA-00942: table or view does not exist Eg: SELECT * FROM v_new; • When we execute this particular view it will throw an error. ORA-04063: view "HR.V1" has errors.

10. Querying a view • Once your view has been created, you can query the data dictionary view called USER_VIEWS to see the name of the view and the view definition. • The text of the SELECT statement that constitutes your view is stored in a LONG column.

11. Removing a view • You can remove a view without losing data . • The DROP statement removes the view definition from the DB. • Views based on deleted views become invalid. • Only the creator or a user with the DROP ANY VIEW privilege can remove a view. • We can remove the error of view by dropping the view or by creating a table . SELECT * FROM USER_ERROR; • Syntax Drop view <viewname>; Eg: Drop view v_new; View dropped.

12. Inline views • An inline view is a subquery with an alias that you can use within a sql statement. • Subquery used in the FROM clause is called inline view. Eg: Select MAX(salary),MIN(salary) FROM (SELECT f_name,salary from emp where d_id=60);

13. Materialized view • It is a snapshot of a remote table data. • It is a database object. • Data manipulation cannot be done here. • The tables in the query are called master tables or detail table. • To store a query view is used and to store a result materialized view is used. • Sysntax: Create materialized view mv AS SELECT f_name, salary from employees WHERE d_id=90; Select * from mv; • When you update a table to refresh the materialized view we use DBMS_MVVIEW.REFRESH(‘mv’,’C’); C means complete refresh. • To check all the materialized views created we use SELECT * FROM USER_MVVIEW;

14. Drop materialized view • We can delete a materialized view using syntax DROP MATERIALIZED VIEW <view_name>; Eg: Drop materialized view mv;

15. Difference between normal view and materialized view Normal view • It is a stored select statement. • It is a virtual component. • It allows DESC,DML,SELECT on it. • It is stored permanently in “user_views” system table. • DML on view are reflected in table and DML on table are reflected in view. • It is used to share “selected rows and columns” with other rows. • It is used for reporting purpose. • It will improve the performance while manipulating or retrieving data through views.

16. Materialized view • It is a static view • It holds data in it • It will not support DML on it. • DML on tale will not reflect in view • To create it “create materialized view” permission is required. • It is used to maintain historic data. • It is used for data analysis and reporting purpose. • It is same as SNAP SHOT.