Views

Understanding Views:

To understand views let’s take an e.g. of a mirror from our day to day life. We can see exact replica of ourselves  in the mirror i.e. Virtual image. View is a also mirror image of a table.

Defining Views:

Technically speaking, view can be defined as a ‘Virtual Table’ which acts as a table but is not an actual table.To understand the concept of a view, let’s see what happens internally when a view is created.

Internal Working of a View:

Let’s take an EMP table containing 24 records.Out of 24 records, suppose 10 employees in EMP table have salary greater than 2000. Now, create a view for this.

When above query is fired, it does not execute statement ‘SELECT * FROM EMP WHERE SAL > 2000’, instead stores this statement in the System Catalog under the name ‘Emp_V’. Thus, when we create a view, any new table is not created.Instead,only SQL statement containing its definition (SELECT * FROM EMP WHERE SAL > 2000) is stored with a name (Emp_V ), hence views do not occupy any space.

Scenario 1: Retrieving Data from View.

Now, When this query is fired, SQL finds the view definition with name Emp_V in System Catalog  and converts the query into ‘SELECT * FROM EMP WHERE SAL>2000’  i.e SQL knows from the view definition that it has to select data whose salary is greater than 2000. As a result, it shows total 10 records.

Scenario 2: Updating Data in a View.

Now, When this query is fired, SQL finds the view definition with name Emp_V in System Catalog and converts the query into ‘UPDATE EMP SET DEPTNO =20 WHERE SAL>2000’  i.e SQL knows from the view definition that it has to Update data for those whose salary is greater than 2000.let’s run the below query to fetch the data.

By firing either of above Select queries on the view or directly on the table, we can view the updated records i.e. for all those whose salary is > 2000, dept no. will be updated to 20.Thus, updations done on view are actually done on original table internally.

Scenario 3: What happens to view when we Insert  or update records directly into original table.

As a result of above query, a new row is inserted into the EMP table resulting into a total of 25 records now.

Now, When we fire a Select query on view, it displays total 11 records ( 10 old records having salary > 2000 + 1 new inserted record ). Thus, whenever we query a view, Database recreates the data picking up the latest updated records from the original table. Thus, View always shows latest data. Similar is the case with the update query.

Key-Facts about Views:

  • Views are virtual or pseudo tables that are not real tables.
  • Views do not occupy any space as they do not store any data.
  • View always shows latest data.
  • Views are actually SQL statements being stored in the system catalog under a name i.e. the view name.
  • View can be made on a single table ( Simple view ) or a combination of tables (Complex Views).
  • Any changes i.e. update / insert /Delete done on views are actually done on original table.

Create Simple View :

Create Complex View :

Create or Replace View :

Suppose, we want to modify the existing view definition like add or remove columns from the ‘ select statement ‘ of view definition then we use create or replace view command.

Create View on Existing View :

Create View with ‘FORCE’ Option :

Create View ‘WITH CHECK’ Option : 

Update , Insert and Delete operations on View :

  • If a view is created on a Single Table and contains all mandatory columns of the original table, then insert , update , delete are possible on views.
  • If view is created on a Single Table and do not contain all columns with ‘ NOT NULL’ constraint then only update and delete operations can be done, insert is not possible.
  • Update / Insert are only possible on views , if the select clause of the view definition does not contain :distinct clause, set operators , order by clause , multiple tables in from clause, subqueries in where clause ,calculated columns and group by and Having clause.
  • Insert , Update , Delete operations done on View are actually performed on original table.
Dropping a View:
To See View  Definition:
Materialized View:

Difference between Materialized and Normal Views :

Advantages of View :

 

Indexes
Sub Queries [ Uncorrelated Sub Queries ]
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *