VIEWS

Views are logical representations of base tables. They are usually used to:

  • hide data complexity
  • protect base tables
  • hide columns
  • present data differently

They represent a stored SQL statement (SELECT), but the data is still saved in the base tables.

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
[schema.]viewname [(alias [,alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;

OR REPLACE If the view already exists, it will be dropped before being created.
FORCE or NOFORCE The FORCE keyword will create the view even if the detail table(s) in the subquery does not exist. NOFORCE is the default and will cause an error if the detail table does not exist.
WITH CHECK OPTION This is to do with DML. If the subquery includes a WHERE clause, then this option will prevent insertion of rows that wouldn’t be seen in the view or updates that would cause a row to disappear from the view. By default, this option is not enabled, which can give disconcerting results.
WITH READ ONLY Prevents any DML through the view.
CONSTRAINT constraintname This can be used to name the WITH CHECK OPTION and WITH READ ONLY restrictions so that error messages when the restrictions cause statements to fail, will be more comprehensible.

In addition, a set of alias names can be provided for the names of the view’s columns. If not provided, the columns will be named after the table’s columns or with aliases specified in the subquery.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, column3 FROM table_name;

Materialised views

With this views, the data is actually stored in them. We are increasing performance and constant calling of the base tables. It is important to pay attention to refresh the data regularly, to keep them up to date in the materialised view.

Layered views

It is possible to make a view of a view.

CREATE VIEW view1 AS
SELECT * FROM table1;

CREATE VIEW view2 AS
SELECT * FROM view1
WHERE some_column = 100;

SELECT * FROM view2
WHERE other_column = 200;

This would be equal to

SELECT * FROM view1
WHERE some_column = 100 AND other_column = 200;

Droping View
DROP VIEW [schema.]viewname ;

Simple and Complex Views

For practical purposes, classification of a view as simple or complex is related to whether DML statements can be executed against it: simple views can accept DML statements, complex views can’t. The strict definitions are as follows:
– A simple view draws data from one detail table, uses no functions, and does no aggregation.
– A complex view can join detail tables, use functions, and perform aggregations.

It is usually possible to execute DML against a simple view but not always. For example, if the view does not include a column that has a NOT NULL constraint, then an INSERT through the view cannot succeed (unless the column has a default value).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: