views - Oracle Training in Chennai
Download
Report
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.