Neoview SQL Reference Manual (R2.3)

Views
A view provides an alternate way of looking at data in one or more tables. A view is a named
specification of a result table, which is a set of rows selected or generated from one or more base
tables or other views. The specification is a SELECT statement that is executed whenever the
view is referenced.
A view is a logical table created with the CREATE VIEW statement and derived by projecting a
subset of columns, restricting a subset of rows, or both, from one or more base tables or other
views.
SQL Views
A view’s name must be unique among table and view names within the schema that contains it.
You cannot create views with names prefixed by the name of a user metadata table. For example,
you cannot create a view named HISTOGRAMS_MYVIEW.
Single table views are updatable. Multitable views are not updatable.
For information about SQL views, see “CREATE VIEW Statement” (page 88) and “DROP VIEW
Statement” (page 102).
Example of a View
You can define a view to show only part of the data in a table. For example, this EMPLIST view
is defined as part of the EMPLOYEE table:
JOBCODEDEPTNUMLAST_NAMEFIRST_NAMEEMPNUM
1009000GREENROGER1
1001000HOWARDJERRY23
3003000WALKERTIM75
. . .. . .. . .. . .. . .
In this sample view, the columns are EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, and
JOBCODE. The SALARY column in the EMPLOYEE table is not part of the EMPLIST view.
MVGROUPs
An MVGROUP is a group of materialized views that are refreshed together. One way to group
MVs is by the frequency of the REFRESH (for example, each day, each week, each month).
Another way is to create groups to preserve consistency, that is, to group all the MVs on common
tables.
An MV can be added to an MVGROUP only when it satisfies at least one of these conditions:
• all the MVs that are used by it, directly or indirectly, are also in the group
• the MV is single-delta
This ensures that every MV in the group is consistent with a well-defined state of the database
tables after a REFRESH. A single-delta MV is defined as an ON REQUEST MV that can be
refreshed based on changes (delta) to a single table. A single-delta MV must satisfy the
requirement that the MV is based either on a single table (base table or MV) or on a join where
only one of the base tables is not included in the IGNORE CHANGES clause.
MVGROUPS are automatically created for every table used by MVs. Those MVGROUPS have
the same name as their corresponding base table. As MVs are created, they are automatically
added to the MVGROUP of their corresponding base tables. To refresh all the MVs on a specific
base table, use the “MAINTAIN Command” (page 192).
264 SQL Language Elements