SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-16
Determining When to Use Indexes
Using Only Views to Externalize Data
Experienced database administrators have observed that having applications use only
views as the external interface for the database scheme has certain advantages for
both programs and ad hoc queries.
Advantages to using only views as the external database scheme include:
•
The physical database structure of the base tables is not externalized to
programmers or other users.
•
The database file layout can be normalized independent of a program or of a
user’s interpretation.
•
New base tables and views can be easily created and integrated with the existing
scheme.
•
Ad hoc queries are limited to the data returned by the views. Allowing users to use
only views can prevent them from accessing sensitive data.
•
Protection views provide the same performance as the base tables.
•
Protection views can help provide data integrity.
Disadvantages to using only views as the external database scheme include:
•
Backing up views for recovery involves a slightly more complex recovery strategy
to ensure that you save all the view definitions. Views, however, are easily re-
created because they contain no data.
•
Managing the database is more difficult because of the greater number of objects.
To use only views, you should create the views as:
•
For each base table, create a read-only protection view that includes all columns
and rows of the table.
•
For each write operation on a base table or tables, create a protection view.
•
Create shorthand views based on the protection views instead of on base tables.
These shorthand views can be used only for queries.
Determining When to Use Indexes
Indexes are usually used to improve performance. An index is an alternate access path
to a table, which differs from the inherent access path (primary key) or clustering key
defined for the table at creation. Indexes provide alternate-key sequences for files of
any structure: key-sequenced, relative, or entry-sequenced. In general, an index
improves access speed when the data is requested in the order of the index key.
When compiling a statement, SQL/MP selects the query execution plan for a statement
by choosing the best access path to the data. If an index exists, SQL evaluates using
the index. Indexes give the optimizer more possible access options.