RE: DBA Question

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

>From a layman's point of view, you can think of it this way:

1. An INDEX allows you to order records
2. A VIEW allow you to select records
3. A logical file allows both

It's a bit more complicated than that, of course.  But in general an
index can only determine the order of records (it can also be used to
impose uniqueness on a key), while a view can only define the records
and fields to be selected.

A logical file, on the other hand, allows both ordering and selection.
It also has the ability to specify the maintenance mode, allowing you to
keep the index current at all times (small overhead on every write) or
to defer the build of the index to when it is needed (large overhead on
open, or at IPL).

Logical files are more record-oriented and allow positioning by key,
including partial key, as well as easy update of individual records.
SQL cursors over indexes and views allow update of the current record,
but also allow a single statement to update multiple records.  However,
positioning the cursor within an index or a view is not as flexible as
positioning within a logical file.

Different databases have different implementations of indexes, views and
indexes over view.  For example, SQL Server creates a persistent B-tree
index when you create an index over a view.  Thus, performance for
various combinations of index and view are somewhat dependent on the
database.

Joe

> From: Jack Derham
> 
> Got into a discussion this afternoon about what the real differences
are
> between logical files, views and indexes. The discussion got a little
> heated
> at times so I would ask that some of you well versed members to please
> help
> out less fortunate uninformed members about the real physical and
logical
> attributes of these object types.