Changes characteristics of an index.

 You can change the following:

  o  The description text

  o  Physical characteristics of a sorted index

  o  Storage map associated with an index

 You cannot change a sorted index to a hashed index or vice versa.

 Example:

      RDO> CHANGE INDEX JH_EMPLOYEE_ID
      cont> NODE SIZE 200 PERCENT FILL 80.

Additional information available:

MoreFormatExamples

 To change an index with the CHANGE INDEX statement, you need the
 Rdb/VMS CHANGE privilege for the relation.

 You must execute this statement in a read/write transaction.  If
 there is no active transaction and you issue this statement, Rdb/VMS
 starts a read/write transaction implicitly.

 Other users are allowed to be attached to the database when you issue
 the CHANGE INDEX statement.

 CHANGE INDEX name ─┬───────────────────────────────┬─┐
                    └─> 
D
E
S
C
R
I
P
T
I
O
N typebox (I)typebox (S) typebox (/)typebox (*) text */ ──┘ │
 ┌─────────────────────────────<──────────────────────┘
 └┬──────────────>───────────┬─┬─────────────>─────────┬─> .
  └> sorted-index-param-list ┘ └> index-storage-clause ┘

Additional information available:

nametextsorted-index-paramindex-storage-clause

 The name of the index you want to change.

 A text string that adds a comment to the index definition.

 sorted-index-param-list =

  ────┬────┬──────────────────>──────────────────────┬───┬─────────>
      │    ├─> 
N
O
D
E 
S
I
Z
E ─────> number-bytes ────────┤   │
      │    ├─> 
P
E
R
C
E
N
T 
F
I
L
L ──────> percentage ──────┤   │
      │    └─> 
U
S
A
G
E ─────────┬─────> 
U
P
D
A
T
E ───┬────┘   │
      │                       └─────> 
Q
U
E
R
Y ────┘        │
      │                                                  │
      └────────────────────<─────────────────────────────┘

Additional information available:

NODE-SIZEPERCENT-FILL

USAGE-mode

 The size of each index node.  The number and level of the resulting
 index nodes depend on this value, the number and size of the index
 keys, and the value of the PERCENT FILL clause.  If you omit this
 clause in a CHANGE INDEX statement, the existing value is used.

 Sets the initial percentage of fullness for each node in the index
 structure.  The valid range is 1 to 100 percent.

 USAGE UPDATE, the default, sets the percent fullness of each index
 node at 70 percent.  USAGE QUERY sets this value at 100 percent.
 Supplying PERCENT FILL and the USAGE option is allowed in the syntax;
 however, the USAGE option takes precedence over an explicit PERCENT
 FILL value.

 index-storage-clause =

 STORE ─┬> index-within-clause ──────────────────────────┬─┐
        └> 
U
S
I
N
G ─┬> field-name ─┬> index-within-clause ─┘ │
                  └───── , <─────┘                         │
  ┌────────────────────────<───────────────────────────────┘
  └─┬─────────────>──────────────┬──>
    └> default-threshold-clause ─┘

 Allows you to specify a storage map definition for the index.  You
 can use the index-storage-clause only in a multifile database.  This
 clause allows you to specify which storage area files will be used to
 store the index entries:

  o  All index entries can be associated with a single storage area.

  o  Index entries can be systematically distributed, or partitioned,
     among several storage areas by specifying upper limits on the
     values for a key in a particular storage area.

 If you omit the storage map definition, the default is to store all
 the entries for an index in the main RDB$SYSTEM storage area.

 You should define a storage area for an index that matches the
 storage map for the relation with which it is associated.  In
 particular, under the following conditions, the database system will
 store the index entry for a row on or near the same data page that
 contains the actual row:

  o  The storage areas for a relation have a mixed page format.

  o  You specify an identical storage clause for the index as exists
     in the storage map for the relation.

  o  The storage map for the relation also names the index in the
     PLACEMENT VIA INDEX clause.

 Such coincidental clustering of indexes and rows can reduce
 input/output operations.  With hashed indexes and coincidental
 clustering, the database system can retrieve rows for exact match
 queries in one input/output operation.

 For sorted indexes, specifying an identical storage map reduces
 input/output contention on index nodes.

Additional information available:

index-within-clausefield-namedefault-threshold-clause

 index-within-clause =

 WITHIN ─┬─> area-name ─┬──────────────>───────────────┬─┐
         │              └> 
W
I
T
H 
L
I
M
I
T typebox (O)F ─┬> literal ─┬┘ │
         │                                └──── , <───┘  │
         │ ┌────────────────────────<────────────────────┘
         │ └─┬──────────>──────────┬──┬─────────>
         │   └─> threshold-clause ─┘  │
         └───────────── typebox (;) <───────────┘

Additional information available:

WITH_LIMIT_OF

area-namethreshold-clause

 The name of the storage area in which you want the index stored.  You
 must have defined this storage area with either the DEFINE DATABASE
 statement or the DEFINE STORAGE AREA clause of the CHANGE DATABASE
 statement before you refer to it in the map-storage-clause clause.

 If the index is a hashed index, the storage area must have a MIXED
 page format.

 The maximum value for the index key that will reside in the specified
 storage area.  For multisegmented index keys, specify a literal value
 for each field.

 The number of literals in this clause must be less than or equal to
 the number of fields in the USING clause.  Repeat this clause to
 partition the index entries among multiple storage areas.

 When you are modifying a multisegmented index using multisegmented
 keys and use the STORE USING...WITH LIMITS clause, if the values for
 the first key are all the same, then set the limit for the first key
 at that value.  By doing this, you ensure that the value of the
 second key determines the storage area in which each record will be
 stored.  Note that the last storage area you specify CANNOT have a
 WITH LIMIT OF clause associated with it.

 threshold-clause=

 ──> 
T
H
R
E
S
H
O
L
D
S typebox (A)typebox (R)typebox (E) ──────────┐
  ┌─────────────<─────────────┘
  └─> ( ──> val1 ─┬───────────>──────────┬─> ) ──>
                  └─> ,val2 ──┬─────>────┤
                              └─> ,val3 ─┘

 Specifies associated threshold values for each storage area with
 uniform format that is specified in the index-within-clause.  By
 setting threshold values, you can make sure that Rdb/VMS does not
 overlook a page with sufficient space to store compressed data.

 The threshold values specify when the page is marked as FULL in the
 SPAM page free space inventory lists.  For example, if you set
 default values of 70, 85, and 95 percent, ranges of guaranteed free
 space on each data page are 30, 15, and 5 percent, respectively.  If
 you do not set default values, the values are (0,0,0).  With values
 of (0,0,0), Rdb/VMS will use the record length when setting the SPAM
 fullness.  Rdb/VMS will never store a record on a page at threshold
 3.  The value you set for the highest threshold can be used to
 reserve space on the page for future record growth.

 If you specify a value of 40 for the "val1" parameter, but do not
 specify values for the "val2" or "val3" parameters, the threshold
 values will be set at (40,100,100).

 If you use data compression, you should use logical area thresholds
 to obtain optimum storage performance.

 The name of the field whose value will be used as a limit for
 partitioning the index across multiple storage areas.

 default-threshold-clause =

 ──> 
D
E
F
A
U
L
T 
T
H
R
E
S
H
O
L
D
S typebox (A)typebox (R)typebox (E) ──┐
  ┌─────────────<─────────────┘
  └─> ( ──> val1 ─┬───────────>──────────┬─> ) ──>
                  └─> ,val2 ──┬─────>────┤
                              └─> ,val3 ─┘

 Specifies associated threshold values for each storage area with
 uniform format that is NOT specified in the index-within-clause.  By
 setting threshold values, you can make sure that Rdb/VMS does not
 overlook a page with sufficient space to store compressed data.

 The threshold values specify when the page is marked as FULL in the
 SPAM page free space inventory lists.  For example, if you set
 default values of 70, 85, and 95 percent, ranges of guaranteed free
 space on each data page are 30, 15, and 5 percent, respectively.  If
 you do not set default values, the values are (0,0,0).  With values
 of (0,0,0), Rdb/VMS will use the record length when setting the SPAM
 fullness.  Rdb/VMS will never store a record on a page at threshold
 3.  The value you set for the highest threshold can be used to
 reserve space on the page for future record growth.

 If you specify a value of 40 for the "val1" parameter, but do not
 specify values for the "val2" or "val3" parameters, the threshold
 values will be set at (40,100,100).

 If you use data compression, you should use logical area thresholds
 to obtain optimum storage performance.

 Example 1

 The following example changes the index node size to 100 bytes, and
 sets the initial fullness percentage from 70 percent to 95 percent:

 RDO> CHANGE INDEX JH_EMPLOYEE_ID
 cont>  NODE SIZE 100
 cont>  PERCENT FILL 95.

 Note that JH_EMPLOYEE_ID is a sorted index.  You cannot change node
 size, percent fill, or the USAGE clause for a hashed index.

 Example 2

 The following example changes how the index is stored by specifying a
 new index-storage-clause for the index:

 RDO>    CHANGE INDEX EMPLOYEES_HASH
 cont>    DESCRIPTION IS /* Hashed index for employees */
 cont>    STORE USING EMPLOYEE_ID
 cont>    WITHIN
 cont>     EMPIDS_LOW WITH LIMIT OF "00400";
 cont>     EMPIDS_MID WITH LIMIT OF "00800";
 cont>     EMPIDS_OVER.



 Example 3

 The following example changes the size of each index node and sets
 the initial fullness percentage for each node in the index structure
 being changed.  It also specifies a storage map definition for the
 index.

 RDO>     CHANGE INDEX COLL_COLLEGE_CODE
 cont>    NODE SIZE 1250 PERCENT FILL 100
 cont>    STORE WITHIN EMP_INFO.