SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• 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.
When compiling a statement, NonStop SQL/MX selects the query execution plan for a statement
by choosing the best access path to the data. If an index exists, NonStop SQL/MX evaluates using
the index. Indexes give the optimizer more possible access options.
Each index is assigned a name and is physically stored in a separate key-sequenced file. Index
files are not tables, and they cannot be queried directly through NonStop SQL/MX. They are only
a tool for providing faster access to tables.
Precreating Indexes or Managing Constraint-Created Indexes
NonStop SQL/MX automatically creates an index for you whenever you create a UNIQUE
constraint, droppable PRIMARY KEY constraint, or REFERENTIAL INTEGRITY constraint. The
constraint, in turn, uses the created index to validate the constraint’s operation. An index that is
created when you create a constraint uses the table’s primary partition’s attributes with respect to
size and location. The index is created unpartitioned.
To specify your own attributes for an index:
• If you have not yet created the constraint that requires a supporting index, first create the index
using the desired attributes, then create the constraint.
• If you have already created the constraint and want to change the attributes of the supporting
index, use ALTER TABLE to change its MAX EXTENTS and use MODIFY to change its location
or to add one or more partitions.
Creating Indexes With the NO POPULATE Option
If you need to create an index on a table that is already populated, use the NO POPULATE option,
and then run the POPULATE INDEX utility to load the index. Because CREATE INDEX executes in
a single TMF transaction, it could experience TMF limitations (for example, transaction timeout) if
a large amount of data must be moved. For more information, see the SQL/MX Reference Manual.
Performance Benefits of Indexes
Indexes can improve performance by eliminating the need for the disk process to access the
underlying table. If the query can be satisfied by the columns contained in the index, the underlying
table is not accessed. By using only the index, you reduce I/O to the table.
For example, consider this query in which ATABLE has a unique index named AINDEX, which
contains columns A and B, in that order, from ATABLE:
SELECT A,B FROM ATABLE;
The query can be satisfied by accessing only AINDEX, which contains all the columns requested.
This type of index-only retrieval can be effective on both unique and nonunique indexes.
Determining a Database Layout 37