What is a View in oracle?


View in Oracle

A view in Oracle database is simply a SELECT query that is given a name and stored in memory for later use. A view can be made joining one or more table and later be queried like a virtual table.

How to Create a View

Suppose we have the following query.

SELECT customerid, customername
FROM customers WHERE countryid='US';
Now we can create a view with this query as follows

CREATE VIEW us_customers
AS
SELECT customerid, customername
FROM customers WHERE countryid='US';
Now, this view can be used instead of the full query

SELECT customerid, customername
FROM us_customers WHERE customerId > 50;
Oracle will transform the query into following one

SELECT * 
FROM (select customerid, customername from customers WHERE countryid='US') 
WHERE customerid BETWEEN 100 AND 200

How to Update a View

To update a view we can use CREATE OR REPLACE VIEW statement to update a view

 
CREATE OR REPLACE VIEW us_customers
AS
SELECT customerid, customername, customerAge
FROM customers WHERE countryid='US';

How to Delete a View

We can use DROP VIEW statement to delete a view

DROP VIEW us_customers

Benefits of Using a View 

  • Provide a simpler interface: just query the view, and not a dozen tables, doing joins and all
  • Provide an interface that doesn't change (or less often) :
  • Even if you change the structure of the tables, you might be able to modify your view so it still returns the same thing
  • Which means no change is needed in your application's code: it'll still work, as it's using the view, and not directly be accessing the tables
  • Only provide an interface to some fields of the tables
  • No need for the users to see some data they won't use
  • Or to access some data they should not use
  • With some database engines (I think MS SQL Server supports that), some type of views can have indexes
  • Which is a good thing for performances: if you have some complex query, store it as a view, and define the required indexes on that view

Comments

Post a Comment

Popular posts from this blog

Handling Exception Using Spring's Aspect Oriented Programming

In this post, I will show you how we can handle exceptions using springs aspect-oriented programming. Prerequisites To follow this article you should have JDK 8+   gradle 2.3 +  Your favorite IDE (I used IntelliJ Idea) Folder Structure The folder structure should be as follows  aopexeptionhnadling ├── build.gradle └── src     ├── main     │   └── java     │       └── net     │           └── asifhossain     │               └── aopexceptionhandling     │                   ├── aop     │                   │   └── ExceptionLoggerPointCut.java     │                   ├── AopExampleApp.java     │                   └── service     │                       └── ExceptionalService.java     └── test         └── java             └── net                 └── asifhossain                     └── aopexceptionhandling                         └── AopExampleAppTest.java Creating Gradle Build File Create a file named build.gradle in application with the following content build.gradle Creating the se
Read more

Refactoring Comparators in Java 8

In this article, I will show how  we  can refactor our old ugly comparators to cool elegant one. Example Domain Class Employee { private int salary; private int yearsOfExperience; int getSalary() { return salary; } public int getYearsOfExperience() { return yearsOfExperience; } } Use Case Sort a list of  Employee  class by  salary ,  yearsOfExperience  etc. Java 7 Code Sorting By Salary employees.sort(new Comparator<Employee>() { @Override public int compare(Employee o1, Employee o2) { return o1.getSalary() - o2.getSalary(); } }); Sorting By Years of Experience employees.sort(new Comparator<Employee>() { @Override public int compare(Employee o1, Employee o2) { return o1.getSalary() - o2.getSalary(); } }); This code looks ugly. Actually, it became ugly after the introduction of lambda operator. Refactoring in Java 8 Java8  introduced a new operator
Read more