Views in Oracle SQL

  • A VIEW in SQL is a logical subset of data from one or more tables. 
  • View can be used to restrict data access and also to represent the same data in multiple format.
  • We can create a view with read-only option to restrict access to the view.
  • Force option creates a view even if the table is not present.
  • Views will not hold data of its own, instead, it retrieves the data from the base table/view (expect materialised view)
  • Triggers can be created on views

Read only syntax :

CREATE or REPLACE FORCE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition WITH read-only;

All views in a database

select * from user_views;

Types of View :

Simple Views

  • Simple view are based of single table.
  • The records are not group. (No grouping are allowed in Simple View).
  • Does not contain a distinct keyword or pseudocolumns or expression based column.
  • DMLs are allowed in Simple Views

Complex Views

  • Can have any numbers of tables joined
  • Records can be grouped. Grouping functions like Min, Max, Count, Sum, Avg can be used
  • Can contain “Distinct” or PSEDUDOCOUMNS and columns based expressions.

Materialized Views

  • In materialised view, the data is actually stored in it(at a physical location) unlike View where the underlying query is executed when the view is called at the runtime.
  • Even if the data from the main table is deleted the data from the materialised view is not deleted, you need to call a refresh to job manually to refresh the view.

Leave a Comment