SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-42
Creating Indexes on Base Tables
This example shows a protection view on the table $VOL4.APPLTAB.EMPLOYEE. The
owner of the underlying table is the same as the owner of this view. The security of the
view enables any network user to read the view and any local group 200 user to
update or insert rows into the view.
$VOL3.APPLTAB.PREMPV2 Owner = 200,255
Security = "NG-O"
This example shows a shorthand view on the table $VOL4.APPLTAB.EMPLOYEE and
other tables. The owner of the view can be different from the owner of the underlying
tables. The security of the shorthand view is not the basis for access. Read access to
the underlying tables authorizes access to this view. For this example, only group 200
users can read the view because $VOL4.APPLTAB.EMPLOYEE is secured for group
200 users.
$VOL1.APPLTAB.SHEMPV1 Owner = 100,255
Security = "NNNO"
Creating Indexes on Base Tables
An index provides an alternate access path to a table; the alternate path is different
from the inherent access path (primary key). Indexes can improve application
performance for data retrieval operations by providing the optimizer with a greater
choice of access paths. Indexes can be scanned forward or backward.
If an existing index includes the selection columns for an SQL statement, the SQL
compiler uses the index as an access path to the data. For more information about
indexes and performance, see Determining When to Use Indexes on page 3-16,
Optimizing Index Use on page 14-16, and Maximizing Parallel Index Maintenance on
page 14-17.
Creating an Index
To create an index, use the CREATE INDEX statement, which creates both the index
definition in the catalog and the physical file. If the underlying table contains data, the
creation process automatically loads the index. CREATE INDEX statements refer to
existing columns of a base table and create alternate indexes on the specified
columns.
When you define an alternate index, first consider the column-related guidelines
described under Defining Columns
on page 5-19.
Also consider primary key definitions, as noted in Primary Keys on page 3-2 and
Creating Key-Sequenced Tables on page 5-11. Determine if the primary key is the
most appropriate based on actual use of the table.