At 09:52 PM 30/06/2005 +1000, you wrote:

>Hi all,
>
>This may seem odd but what is a "View"

At its simplest, a view is a table specification that stores no data. It
acts as a filter on
both the columns and the rows of the underlying tables referenced in the view—a
“window” through which actual data is exposed. The query that defines the
view can
be from one or more tables or other views in the current database. It
behaves in many
ways like a persistent table and encapsulates some special extensions to
link it with the
underlying tables.

You query a view as if it were an ordinary table and perform joins, order
and group
output, specify search conditions, subquery it, derive runtime columns from
its virtual
data, process a named or unnamed cursor selected from it, and so on.

Many views can be “updated,” thereby modifying the state of the underlying
per-sistent
tables, or they can be made updatable through triggers. When changes to the
tables’ data are committed, the data content of the view changes with them.
When a
view’s data changes are committed, underlying tables’ data changes accordingly.

Keys and Indexes

Views cannot have keys or indexes. The underlying tables, known as base
tables, will be
used as the sources of indexes when the optimizer constructs query plans.
The topic of
query plans for queries involving views is quite complicated. It is
discussed later in this
chapter, in the section “Using Query Plans for Views.”

Row Ordering and Grouping

A view definition cannot be ordered. An exception is thrown if an ORDER BY
clause is
included. Consequently, it does not make sense to use the FIRST and/or SKIP
quanti-fiers
for SELECT, since they operate on ordered sets.
A grouped query specification (using a legal GROUP BY clause) is fine.
(That's an excerpt from Chapter 24 of TFB).

>Where would I use them and why ?

Wherever you could use a table. One use is when you want to present a
subset of one or more tables and you don't want to make all of the data
available to a group of users. Don't give table privileges to those
users: give them view privileges instead. Another is where you want to be
able to form a repeatable query, from which you want to abstract data in a
way that would be awkward or impossible with a direct query...et al.

Why? From a development POV, it gives you a whole other range of ways to
extract and abstract data. You can manipulate data in underlying tables
through a view, which can add strength to data integrity and security. Why
not? is a pertinent question...there *is* more overhead for the optimizer
when setting up the plan for a view that is joined to other sets (tables,
views). You can define virtual fields in views by computing them - handy,
but these fields can't be indexed, so searching or ordering on them might
be slow if the set is fairly large. A view definition can't be altered,
either, so design with care, especially if you intend to use these views in
other structures that will depend on them.

It's definitely worth putting views in your armoury.

./hb