Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 69

training set, those can be used in the definition of the view, in place of or
in addition to the md5 function used above.
A view is not limited to a subset of any one table. A view can be cre-
ated using a complex SQL statement involving a join of multiple tables.
Using the pubchem example above, a view might be created as:
Create View nci_h23_set1 As Select
n.sid, n.activity_outcome, n."log_gi50_M", n.log_gi50_ugml,
s.ext_datasource_name, s.ext_datasource_regid,
sc.compound_id, sc.compound_type,
c.openeye_can_smiles
From
nci_h23 n
Join substance s On s.substance_id = n.sid
Join substance_compound sc On sc.substance_id = s.substance_id
Join compound c On c.cid = sc.compound_id
This view can be used to simplify selections. For example, the SQL statement
Select compound_id, "log_gi50_M" From nci_h23_set1
Where activity_outcome = 2 Order By sid;
is much easier to read (and maintain) than the analogous, lengthy state-
ment in the previous section of this chapter. Even if the entire set repre-
sented by the view nci _ h23 _ set1 is never used, the definition of the
set is very useful. The definition of the view can be changed at any time to
allow for more columns from the original tables or to accommodate any
other change to the definition of the view.
The view itself is not stored as a copy of the subset of the table.
Rather, the view is a dynamic representation of the subset that changes
as rows of the corresponding tables are updated, inserted, or deleted.
A view is analogous to a program or a function that is executed when
necessary to provide a result. It is stored in a schema in the database
and can be used anywhere in an SQL statement that a table can be used.
However, it is not possible to insert rows into or to update rows of a
view. Instead, the original table or tables containing the data must be
updated.
When a view is used, the selection contained in the view is executed
each time the view is used. This can be time-consuming. It is possible
to create a new table that is a real copy of the view. This is sometimes
called a materialized view . This can speed up SQL statements that use
the view. There is an automated procedure in Oracle to maintain mate-
rialized views, with the frequency of the copy set by the user. There is
currently (April 2008) no automatic procedure in PostgreSQL to main-
tain materialized views, although there are suggestions for how to do
this. 7