SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-59
Considerations for CREATE INDEX
ATTRIBUTE[S] attribute [,attribute]...
specifies file attributes for the key-sequenced file that holds the index. In an
ATTRIBUTES clause that is within a PARTITION clause, you must separate
attributes with a space. In ATTRIBUTES clauses in other places, you can
separate attributes with either a space or a comma. File attributes you can
specify are:
Attributes marked with an asterisk (*) default to the same value as the
corresponding attribute in the underlying table. For more information, see the entry
for a specific attribute.
Considerations for CREATE INDEX
If you are creating an index on a large SQL/MX table that is already populated, you
should 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 such as a transaction timeout if a large amount of
data is to be moved. See the SQL/MX Installation and Management Guide for
information about creating and populating indexes.
After you perform POPULATE INDEX, you should perform a FUP RELOAD on the
index and all its partitions, to organize the index structure more efficiently and to
reduce index levels.
If the MAXEXTENT value that you specified is too small, when you run the POPULATE
INDEX utility it automatically increases the value to the largest possible size. When
POPULATE INDEX completes it adjusts the MAXEXTENTS value to the value you
specified, if it is greater than the number of extents that needed to be allocated. If the
number of extents that needed to be allocated is greater than the value you specified,
POPULATE INDEX adjusts the value for MAXEXTENTS to a value equal to the
number of extents that it allocated, plus 50. This is similar to NonStop SQL/MP’s
behavior.
ALLOCATE/DEALLOCATE
on page 8-2
Controls amount of disk space allocated.
AUDITCOMPRESS on
page 8-3
Controls whether unchanged columns are included
in audit records.
BLOCKSIZE on page 8-4 Sets size of data blocks. 4096 is the only supported
value.
CLEARONPURGE on
page 8-5 *
Controls disk erasure when index is dropped.
EXTENT on page 8-6 Controls size of extents that are allocated on disk.
MAXEXTENTS on
page 8-8
Controls maximum disk space to be allocated.