1  CHANGE_INDEX
   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.
 

2  More
   To change an index with the CHANGE INDEX statement, you need the
   Oracle Rdb 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,
   Oracle Rdb starts a read/write transaction implicitly.

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

2  Format

 (B)0CHANGE INDEX name qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
                    mq> DESCRIPTION IS /* text */ qqj x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
 mwqqqqqqqqqqqqqq>qqqqqqqqqqqwqwqqqqqqqqqqqqq>qqqqqqqqqwq> .
  m> sorted-index-param-list j m> index-storage-clause j

 

3  name
   The name of the index you want to change.
 

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

3  sorted-index-param

 (B)0sorted-index-param-list =
 
  qqqqwqqqqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqwqqqwqqqqqqqqq>
      x    tq> NODE SIZE qqqqq> number-bytes qqqqqqqqu   x
      x    tq> PERCENT FILL qqqqqq> percentage qqqqqqu   x
      x    mq> USAGE qqqqqqqqqwqqqqq> UPDATE qqqwqqqqj   x
      x                       mqqqqq> QUERY qqqqj        x
      x                                                  x
      mqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj

 

4  NODE-SIZE
   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.
 

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

4  USAGE-mode
   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.
 

3  index-storage-clause

 (B)0index-storage-clause =
 
 STORE qw> index-within-clause qqqqqqqqqqqqqqqqqqqqqqqqqqwqk
        m> USING qw> field-name qw> index-within-clause qj x
                  mqqqqq , <qqqqqj                         x
  lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
  mqwqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqq>
    m> default-threshold-clause qj

   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.
 

4  index-within-clause

 (B)0index-within-clause =
 
 WITHIN qwq> area-name qwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqk
         x              m> WITH LIMIT OF qw> literal qwj x
         x                                mqqqq , <qqqj  x
         x lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj
         x mqwqqqqqqqqqq>qqqqqqqqqqwqqwqqqqqqqqq>
         x   mq> threshold-clause qj  x
         mqqqqqqqqqqqqq ; <qqqqqqqqqqqj

 

5  area-name
   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.
 

5  WITH_LIMIT_OF
   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.
 

5  threshold-clause

 (B)0threshold-clause=
 
 qq> THRESHOLDS ARE qqqqqqqqqqk
  lqqqqqqqqqqqqq<qqqqqqqqqqqqqj
  mq> ( qq> val1 qwqqqqqqqqqqq>qqqqqqqqqqwq> ) qq>
                  mq> ,val2 qqwqqqqq>qqqqu
                              mq> ,val3 qj

   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 Oracle Rdb 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), Oracle Rdb will use the
   record length when setting the SPAM fullness. Oracle Rdb 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.
 

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

4  default-threshold-clause

 (B)0default-threshold-clause =
 
 qq> DEFAULT THRESHOLDS ARE qqk
  lqqqqqqqqqqqqq<qqqqqqqqqqqqqj
  mq> ( qq> val1 qwqqqqqqqqqqq>qqqqqqqqqqwq> ) qq>
                  mq> ,val2 qqwqqqqq>qqqqu
                              mq> ,val3 qj

   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 Oracle Rdb 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), Oracle Rdb will use the
   record length when setting the SPAM fullness. Oracle Rdb 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.
 

2  Examples
   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.