This section covers Oracle View which is an essential component in Oracle Database.
Introduction to the Oracle View
The result of a query is a derived table as shown in the following example:
SELECT name, credit_limit FROM customers;
The derived table consists of the
name and credit limit (
credit_limit) columns with many rows. It contains only partial data from the
If you give this query a name, then you have a view. This is why sometimes a view is referred to as a named query.
So by definition, a view is a “virtual” table whose data is the result of a stored query, which is derived each time when you query against the view.
A view is a virtual table because you can use it like a table in your SQL queries. Every view has columns with data types so you can execute a query against views or manage their contents (with some restrictions) using the
Unlike a table, a view does not store any data. To be precise, a view only behaves like a table. And it is just a named query stored in the database. When you query data from a view, Oracle uses this stored query to retrieve the data from the underlying tables.
Suppose, we assign the query above a name called
customer_credits and query data from this view:
SELECT * FROM customer_credits;
Behind the scenes, Oracle finds the stored query associated with the name
customer_credits and executes the following query:
SELECT * FROM ( SELECT name, credit_limit FROM customers );
In this example, the
customers table is called the base table. Also, a query that defines the view is called a defining query.
The result set returned from the
customer_credits view depends on the data of the underlying table, which is the
customers table in this case. The
customer_credits view is also dependent on the structure of the
customers table. If you rename or drop one of the columns referenced by the query such as
credit_limit, the view
customer_credits will not work anymore.
When to use the Oracle view
You can use views in many cases for different purposes. The most common uses of views are as follows:
- Simplifying data retrieval.
- Maintaining logical data independence.
- Implementing data security.
Simplifying data retrieval
Views help simplify data retrieval significantly. First, you build a complex query, test it carefully, and encapsulate the query in a view. Then, you can access the data of the underlying tables through the view instead of rewriting the whole query again and again.
The following query returns sales amount by the customer by year:
SELECT name AS customer, SUM( quantity * unit_price ) sales_amount, EXTRACT( YEAR FROM order_date ) YEAR FROM orders INNER JOIN order_items USING(order_id) INNER JOIN customers USING(customer_id) WHERE status = 'Shipped' GROUP BY name, EXTRACT( YEAR FROM order_date );
This query is quite complex. Typing it over and over again is time-consuming and potentially cause a mistake. To simplify it, you can create a view based on this query:
CREATE OR REPLACE VIEW customer_sales AS SELECT name AS customer, SUM( quantity * unit_price ) sales_amount, EXTRACT( YEAR FROM order_date ) YEAR FROM orders INNER JOIN order_items USING(order_id) INNER JOIN customers USING(customer_id) WHERE status = 'Shipped' GROUP BY name, EXTRACT( YEAR FROM order_date );
By adding the following clause:
CREATE OR REPLACE VIEW customer_sales AS
Now, you can easily retrieve the sales by the customer in 2017 with a more simple query:
SELECT customer, sales_amount FROM customer_sales WHERE YEAR = 2017 ORDER BY sales_amount DESC;
Maintaining logical data independence
You can expose the data from underlying tables to the external applications via views. Whenever the structures of the base tables change, you just need to update the view. The interface between the database and the external applications remains intact. The beauty is that you don’t have to change a single line of code to keep the external applications up and running.
For example, some reporting systems may need only customer sales data for composing strategic reports. Hence, you can give the application owners the
Implementing data security
Views allow you to implement an additional security layer. They help you hide certain columns and rows from the underlying tables and expose only needed data to the appropriate users.
Oracle provides you the with
REVOKE commands on views so that you can specify which actions a user can perform against the view. Note that in this case, you don’t grant any privileges on the underlying tables because you may not want the user to bypass the views and access the base tables directly.
More on Oracle View
Follow the tutorials below to learn more about the Oracle View:
- Creating a view – use the
CREATE VIEWstatement to create a new view.
- Drop a view – use the
DROP VIEWstatement to drop a view from the database.
- Updatable views – discuss how to create updatable views.
- Inline views – learn how to use inline views to simplify complex queries and condense several separate queries into one query.
WITH CHECK OPTION– how to protect the view using the
WITH CHECK OPTIONclause of the
- Materialized view – covers the materialized views that help you improve query response times.