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.
1 – 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)0[m[4mCHANGE[m [4mINDEX[m name qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
mq> [4mDESCRIPTION[m IS /* text */ qqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqq>qqqqqqqqqqqwqwqqqqqqqqqqqqq>qqqqqqqqqwq> .
m> sorted-index-param-list j m> index-storage-clause j
2.1 – name
The name of the index you want to change.
2.2 – text
A text string that adds a comment to the index definition.
2.3 – sorted-index-param
(B)0[msorted-index-param-list =
qqqqwqqqqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqwqqqwqqqqqqqqq>
x tq> [4mNODE[m [4mSIZE[m qqqqq> number-bytes qqqqqqqqu x
x tq> [4mPERCENT[m [4mFILL[m qqqqqq> percentage qqqqqqu x
x mq> [4mUSAGE[m qqqqqqqqqwqqqqq> [4mUPDATE[m qqqwqqqqj x
x mqqqqq> [4mQUERY[m qqqqj x
x x
mqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
2.3.1 – 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.
2.3.2 – PERCENT-FILL
Sets the initial percentage of fullness for each node in the
index structure. The valid range is 1 to 100 percent.
2.3.3 – 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.
2.4 – index-storage-clause
(B)0[mindex-storage-clause =
[4mSTORE[m qw> index-within-clause qqqqqqqqqqqqqqqqqqqqqqqqqqwqk
m> [4mUSING[m 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.
2.4.1 – index-within-clause
(B)0[mindex-within-clause =
[4mWITHIN[m qwq> area-name qwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqk
x m> [4mWITH[m [4mLIMIT[m OF qw> literal qwj x
x mqqqq , <qqqj x
x lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj
x mqwqqqqqqqqqq>qqqqqqqqqqwqqwqqqqqqqqq>
x mq> threshold-clause qj x
mqqqqqqqqqqqqq ; <qqqqqqqqqqqj
2.4.1.1 – 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.
2.4.1.2 – 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.
2.4.1.3 – threshold-clause
(B)0[mthreshold-clause=
qq> [4mTHRESHOLDS[m 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.
2.4.2 – field-name
The name of the field whose value will be used as a limit for
partitioning the index across multiple storage areas.
2.4.3 – default-threshold-clause
(B)0[mdefault-threshold-clause =
qq> [4mDEFAULT[m [4mTHRESHOLDS[m 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.
3 – 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.