Database Reference
In-Depth Information
15.2.1 Restriction on use of the Order-By-Clause
When a view is created, the
Order-By-Clause
is not supported in the sub-query. This
means that you cannot create a view that orders data. The rationale for this limitation is
that a view is a virtual relation, and therefore ordering of the data would only increase
system overheads. The obvious response to this argument is this: since a view merely
stores a definition, why not include data ordering in the definition?
The work-around for this limitation is to create the view (obviously without
ordering data), then when the view is being accessed via a query, employ the
Order-By-Clause
on the query.
Example 1:
Creation of the College Bulletin from the college database of
earlier discussions:
15.2.2 Restriction on Data Manipulation for Views
involving UNION, INTERSECT or JOIN
As pointed out in Chapter 13, a view is not updateable if it involves a JOIN, a UNION or
an INTERSECT operation. A little thought will reveal that while this is an understandable
constraint, it is not always a prudent one, as there are situations that could warrant
updateable views involving these operations (for more elaboration, see [Date, 2004]).
The example above is a useful illustration: it should be possible for a user to modify
Pgmname
and/or
Crsnme
for a logical row in
Bulletin
as follows:
•
Pgmname
is modified, the DBMS should use
Pgm#
to
access the correct row in the table
Program
and modify its
corresponding column for
Pgmname
.
If
•
Crsname
is modified, the DBMS should use Crs# to access the
correct row in the table Course and modify its corresponding
column for
Crsname
.
If
In either case, the search for a corresponding column must not be made merely
on the column name (since queries can rename columns), but the name as well as
characteristics of the column (which can be obtained from the system catalog).
Note:
Administering changes (insertion, update or deletion of rows) to join logical
views of this sort is by no means a trivial matter, hence SQL does not support it. The point
to note here is that it is thinkable and indeed doable, though complex. A similar argument
applies to logical views involving UNION and INTERSECT operations.
