Views and Types of View in Oracle #Diksha Oracle Training Centre

                              Views and Types of View in Oracle.

 

Hello Everyone,

In this blog I will discuss about Views and types of Views in Oracle Database .Views plays very important role in database as  Views are Virtual table that can be created from existing or base table. A view can be known as a ‘Stored Query’ or a “Virtual Table”. Views mainly used for security purpose and view used in most places where a table can be used. The table from which view is created are called based table. We can query view just like a table , but views doesn’t required disk space. A view representing a subset of the data present in a table, and performing functions such as joining and simplification of multiple tables into a single table, data aggregation, handling data complexity, providing security etc.

A view can be created by SQL DDL create command i.e Create view statement

View can be created from single base table or can be created from multiple tables by using joins, aggregate function etc. So we divide view into Different Parts.

 

1. Simple View : We can create simple view from one base table :

Syntax is as below

Create view sec_view as select ID, Name from student

Where ID between 1 and 5;

insert into sec_view values(2,'Dheeraj Jha');

update sec_view set name ='Diksha Jha' where ID=2;

Note :  Here we selected only 2 columns that is ID and name , In Simple View we can able to do DML operation i.e. Insert , Update and delete but for the same record only not new one. Important thing in this view is that we can’t manipulate data if that ID or record is not in view or base table. In complex view we can’t to any DML operation .

We can see the view by using simple select statement as below :

Select * from sec_view;

Below is the output of above Syntax:

 

Note : Here sec_view doesn’t required disk space in database.

We can drop the view by using below Syntax:

Drop view sec_view;

 

2 .  Complex View : In Complex view  we can take multiple tables by using joins/subquery and can use several conditions , functions etc. In complex view we cannot do any DML i.e. Insert , update and delete operation so basically used for security purpose .

Note : You will also not able to select  ROWID in complex view .

Let Assume we have 2 tables which we created in my previous  Join blog i.e Student and Student_details table . Now we can create 1 view from both the tables by using joins :

Syntax is as below :

Create view student_sec as Select a.name , a. Id , b.address from student a , student_details b where a.id =b.id ;

// insertion of complex view throw the error

insert into student_sec values('Diksha Jha',2,'DBG');

//Deletion of Complex view throw the error.

delete from student_sec;

To see the data we simply use below select statement.

Select * from student_sec;

Output of above query is as below :

 

Here in one View we have data from two tables and three columns i.e name , id and address with matching records from both the tables so if we want to see the matching records from both tables we simply select this view student_sec and get the data  , no need to make joins from 2 tables also as per our requirement we can create view with multiple base tables. Also the view doesn’t required disk space in database so it’s good for performance as well as it’s not hitting the table while selecting the view.

Note : We cannot  do DML operations in view with commands like insert, update and delete .

Please create some complex view and try to put more condition as per your requirement and play with one view . Since it’s not take disk space so you can do so many experiment with joins from multiple table filter data by using where condition .Since we can’t do any DML operation so it’s good for security purpose as well. Similarly we can remove the view if we don’t need then we use below command to remove the view.

Drop view student_sec;

Advantages of View :

 View is mainly used for security so If we need to maintain any sensitive/important information by providing limited access to the users, views are used. Views mainly used to display the required data, to the users, to keep important/sensitive data safe.

·        Database view is associated with many tables upon which the view is created, it simplifies the complexity of the query.

·        View  used to hide the complexity of the underlying tables used in a database  from the end-users.

·        It’s useful in re-designing the database as it does not affect any application using the same database.

·        The data of the columns from several tables can be calculated very easily when we query the data from the view, as views is enable computed columns.

 

 

Disadvantages

Only Disadvantage I can say for View is when we use changes the table structure frequently on which view is created , so whenever the table structure changed we need to change the view as well also usage of view can cause performance issue in the queries .

Despite the many advantages that the views offer, it still has some disadvantages stated as below:

One of the major disadvantages of using view comes into the picture when we change the table structures frequently upon which the view is created. So when the table structures are changed, the view also needs to be changed.

Also, the usage of view slows down the performance of the queries.

 

There is one another View which is Materialized view which i don’t want to explain in this blog because it’s bit complex but in coming blog when I am going to cover  Materialized view as I will define Materialized view in proper way. But for now I will just give you the overview of Materialized view:

 

Materialized View : It’s not Virtual table , Materialized View is a database object that contains the result of the query . Materialized view are local copies of data located remotely, or are used to create summaries tables based on aggregation of a table’s data . Materialized view also known as snapshot as its stores data based on remote tables.

Uses of Materialized view and creation of Materialized view I will include in next blog .

Main difference between Views and Materialized view . Views are Virtual table and not used any disk space or we can say views are not stored physically. Whereas Materialized view is a Physical copy , database object , picture or snapshot of the base tables.

View is very important in Database specially  for candidate who is preparing for interview. So please go ahead and read this blog and try to create some views then you will see the advantages of View. In this blog I just gave the overview of Materialized view so in coming blogs I will put more information on Materialized View.

Please read this blog carefully and if you have any doubts in this topic, please share your comment in blog.

Thanks