Transcript views - Oracle Training in Chennai

View and Materialized view
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;
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.
Types of views
 Simple views
 Complex views
Difference between simple and complex views
Simple 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;
Complex views
 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;
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;

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
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
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.
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.
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.
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);
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;






Drop materialized view
 We can delete a materialized view using syntax
DROP MATERIALIZED VIEW <view_name>;
Eg:
Drop materialized view mv;
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.
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.