Oracle Views DDL

View Objects

CREATE View

Views are stored queries which are treated as tables. Views can effectively overlay parts of a single table or parts or the whole of multiple tables as a result of a multi-table join. Recently views were always read-only. Oracle views are now updatable, however, with some sever restrictions. The original intention of views was that of hiding or enhancing data structures, making complex queries easier to manage and for the enforcement of security requirements.

• view_name - user name can be used.
• alias - this is an alias for a column in a view. This name can be the same as a column in an underlying table or can be a name for an expression.
• subquery - any valid SELECT statement which does not include an ORDER BY or FOR UPDATE clause. A view can be based on one or more tables.
• WITH CHECK OPTION - specifies that inserts and updates through the view must be selectable from the view. Can also be used in a view based on a view.
• READ ONLY - if set the view can not be changed by INSERT, UPDATE or DELETE operations.
• CONSTRAINT - specifies the name associated with the CHECK OPTION constraint.
• FORCE or NOFORCE
  • FORCE - allows creation of the view if all included objects are not available.
  • NOFORCE - this is the default. All objects must be in place and permission accessible for the view to be created.

ALTER VIEW

DROP VIEW

Commands in Which a View can be Used

• COMMENT
• DELETE
• INSERT
• LOCK TABLE
• UPDATE
• SELECT

What a View's SELECT Statement in the Subquery can not Select

• CURRVAL or NEXTVAL from a sequence.
• No direct access to ROWID, ROWNUM or LEVEL pseudocolumns.

A View can not be Updated if it Does One of the Following

• Contains a join.
• Contains a GROUP BY, CONNECT BY or START WITH clause.
• Contains a DISTINCT clause.
• Contains an expression like (SALARY - (SALARY * TAX_RATE))
• All NOT NULL columns in underlying tables must be in the view and referenced by the update.

A view which contains pseudocolumns or columns modified by expressions can be updated if the update does not affect these columns. Query the USER_UPDATABLE_COLUMNS view to see columns in a view which are designated as updatable.

A Join can have INSERT, UPDATE and DELETE Commands IF ...

• The DML (Data Manipulation Language) affects only one of the tables in the join.
• For UPDATE all of the columns updated are extracted from a key preserved table. If the view has a CHECK OPTION constraint join columns and columns taken from tables which are referenced more than once in the view are shielded from update.
• There is one key-preserved table in a join for DELETE and that table can be present more than once if there is no CHECK OPTION constraint on the view.
• All of the columns are from a key-preserved table for INSERT and the views do not have a CHECK OPTION constraint.

What is a View ?

A view is not stored in the database. Only the SQL statements that form a view's definition is stored in the database. Views contain no data of their own but they appear as normal tables with columns and rows. A view is like a window onto underlying structure which buries the complexity of that underlying structure therebye simplifying the programmers task. Views can serve to join tables and bury the underlying complexity of those tables as shown in the diagram below.

Partition Views

A partition view joins several tables with identical structure into a single entity which can be queried as one. This can allow for the physical partitioning into several table partitions. This allows a table to effectively be spread across several disks. The controls to separate the data over several tables would have to be algorithmically controlled for its proper use by application software.

Partition View Creation

1. Create the tables.
2. Each table should have a constraint which limits the values to be stored in itself.
3. Create an index on the contrained columns of each table.
4. The partition view is created as a series of UNION ALL joined SELECT statements. All columns from all tables should be selected and all these columns must be identical across all tables in the view.
5. Specifiy the view as being parallel if the parallel query option is available. Parallel is much faster than serial scanning.