[database] What is a View in Oracle?

What is a view in Oracle?

A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query. To create a view, you use the SQL syntax:

     CREATE OR REPLACE VIEW <view_name> AS
     SELECT <any valid select query>;

A view is a virtual table, which provides access to a subset of column from one or more table. A view can derive its data from one or more table. An output of query can be stored as a view. View act like small a table but it does not physically take any space. View is good way to present data in particular users from accessing the table directly. A view in oracle is nothing but a stored sql scripts. Views itself contain no data.

If you like the idea of Views, but are worried about performance you can get Oracle to create a cached table representing the view which oracle keeps up to date.
See materialized views

A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query. To create a view, you use the SQL syntax:

     CREATE OR REPLACE VIEW <view_name> AS
     SELECT <any valid select query>;

A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For example, if we frequently issue the following query

SELECT customerid, customername FROM customers WHERE countryid='US';

To create a view use the CREATE VIEW command as seen in this example

CREATE VIEW view_uscustomers
AS
SELECT customerid, customername FROM customers WHERE countryid='US';

This command creates a new view called view_uscustomers. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this:

SELECT * FROM view_uscustomers WHERE customerid BETWEEN 100 AND 200;

And Oracle will transform the query into this:

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

Benefits of using Views

• Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing.
• Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.
• Predicate pushing

You can find advanced topics in this article about "How to Create and Manage Views in Oracle."

A view is a virtual table, which provides access to a subset of column from one or more table. A view can derive its data from one or more table. An output of query can be stored as a view. View act like small a table but it does not physically take any space. View is good way to present data in particular users from accessing the table directly. A view in oracle is nothing but a stored sql scripts. Views itself contain no data.

A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For example, if we frequently issue the following query

SELECT customerid, customername FROM customers WHERE countryid='US';

To create a view use the CREATE VIEW command as seen in this example

CREATE VIEW view_uscustomers
AS
SELECT customerid, customername FROM customers WHERE countryid='US';

This command creates a new view called view_uscustomers. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this:

SELECT * FROM view_uscustomers WHERE customerid BETWEEN 100 AND 200;

And Oracle will transform the query into this:

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

Benefits of using Views

• Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing.
• Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.
• Predicate pushing

You can find advanced topics in this article about "How to Create and Manage Views in Oracle."

regular view----->short name for a query,no additional space is used here

Materialised view---->similar to creating table whose data will refresh periodically based on data query used for creating the view

If you like the idea of Views, but are worried about performance you can get Oracle to create a cached table representing the view which oracle keeps up to date.
See materialized views