SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-12
Determining When to Use Indexes
•
A view can be secured separately from the underlying tables or views. Users of the
view do not need to have access privileges to these underlying objects to access
the view.
Use updatable views to:
•
Provide validity checks on the underlying table for inserts and updates
•
Provide restrictions so that only certain information can be presented to a user by
masking rows and columns of the underlying table from displays or updating. If an
updatable view is defined with the WITH CHECK option, through the view you can
only insert or update rows that the WHERE clause of the view query evaluates to
true.
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 include:
•
The physical database structure of the 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 tables and views can be easily created and integrated with the existing
scheme.
•
Ad hoc queries are limited to data returned by views. Restricting users to using
only views can prevent them from accessing sensitive data.
•
Views provide the same performance as the tables.
•
Views can help provide data integrity.
Disadvantages include:
•
You cannot update views that provide views to more than one table.
•
Managing the database is more difficult because of the greater number of objects.
Determining When to Use Indexes
Use indexes to improve performance and to verify constraints. An index is an alternate
access path to a table, which should differ from the clustering key path defined for the
table at creation. For example, an index might provide shorter access time when the
data is requested in the order of the index key, and the optimizer chooses the index
path over the full table path.