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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-31
Creating Indexes for SQL/MX Tables
Understanding Data Type Correspondence With Host
Languages
For information about data type correspondence between NonStop SQL/MX and the
host programming languages used for embedded SQL/MX, see the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for
Java.
Creating Indexes for SQL/MX Tables
An index provides an alternate access path to a table that differs from the inherent
access path (clustering key). Indexes can improve application performance for data
retrieval operations by providing the optimizer with a greater choice of access paths.
Each row in an SQL/MX index contains:
•
The columns specified in the CREATE INDEX statement
•
The clustering key of the underlying table
An index name is an SQL identifier. Indexes have their own name space within the
schema, so an index name can be the same as a table or constraint name. However,
no two indexes in a schema can have the same name.
If an existing index includes the selection columns for an SQL/MX statement, the
optimizer may use the index as an access path to the data. For more information about
indexes and performance considerations, see Section 4, Understanding and Planning
SQL/MX Tables.
Rules for Creating and Accessing Indexes
An index does not have its own privilege settings. An index always has the same
security as the table it indexes, so only users authorized to access the table can
access the index.
•
To create an index, you must own the schema for the underlying table or be a
super ID user.
•
Whenever you perform an INSERT, UPDATE, or DELETE operation on an indexed
table, you access the index.
•
Whenever you perform a SELECT operation on an indexed table, you can also
access the index. However, you cannot SELECT an index directly, independent of
its table, unless you use an index-only query plan or you force an index-only scan
with CONTROL QUERY SHAPE. For more information about CONTROL QUERY
SHAPE, see the SQL/MX Reference Manual.
For more information about the rules for accessing SQL/MX objects, see Access
Privileges for SQL/MX Database Objects on page 7-9.