Customized data views

A view offers an alternative way of describing data that exists in one or more tables.

Some users might find that no single table contains all the data that they need; rather, the data might be scattered among several tables. Furthermore, one table might contain more data than users want to see or more than you want to authorize them to see. For those situations, you can create views.

You might want to use views for a variety of reasons:

• To limit access to certain kinds of data

  You can create a view that contains only selected columns and rows from one or more tables. Users with the appropriate authorization on the view see only the information that you specify in the view definition.

  For example, you can define a view on the EMP table to show all columns except for SALARY and COMM (commission). You can grant access to this view to people who are not managers because you probably don't want them to have access to this kind of information.

• To combine data from multiple tables

  You can create a view that uses one of the set operators, UNION, INTERSECT, or EXCEPT, to logically combine data from intermediate result tables. Additionally, you can specify either DISTINCT (the default) or ALL with a set operator. You can query a view that is defined with a set operator as if it were one large result table.

  For example, assume that three tables contain data for a time period of one month. You can create a view that is the UNION ALL of three fullselects, one for each month of the first quarter of 2004. At the end of the third month, you can view comprehensive quarterly data.

You can create a view any time after the underlying tables exist. The owner of a set of tables implicitly has the authority to create a view on them. A user with administrative authority at the system or database level can create a view for any owner on any set of tables. If they have the necessary authority, other users can also create views on a table that they didn't create.