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.

ALTER VIEW

DROP VIEW

Commands in Which a View can be Used

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

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

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 ...

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.