(B)0[m[4mDEFINE_INDEX[m name wqqqqqqqqqqqqqqq>qqqqqqqqqqqqwq> FOR relation-name qk
m> [4mDESCRIPTION[m IS /* text */ j x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqwqqqqqqqqq>qqqqqqqqqqwqqqqwqqqqqqqqqqq>qqqqqqqqqqqqwqqqqqqqk
m> duplicates-clause j m> index-storage-clause qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk
tq> [4mTYPE[m IS qqwqqq> [4mHASHED[m qqqj x x
x mqqq> [4mSORTED[m qq> sorted-index-param-list qqqu x
mqqqqqqqqqqqqqq> sorted-index-param-list qqqqqqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqq . <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwq> field-name qqwqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqwqwqqwqk
x x t> [4mASCENDING[m qqqqqqqqqqqqqqqqqqqqqqqu x x x
x x t> [4mDESCENDING[m qqqqqqqqqqqqqqqqqqqqqqu x x x
x x t> [4mSIZE[m [4mIS[m n qqqqqqqqqqqqqqqqqqqqqqqu x x x
x x m> [4mMAPPING[m [4mVALUES[m lo-val [4mTO[m hi-val qj x x x
x mqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj x x
mqqqqqqqqqqqqqqqqqqqqqqqqq . <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqq . <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mq> [4mEND[m wqqq>qqqqw> INDEX q> .
mq> name j
1 – name
The name of the index. You can use this name to refer to the
index in other statements. When choosing a name, follow these
rules:
o Use a name that is unique among all index names in the
database.
o Use any valid OpenVMS name. However, the name cannot end in a
dollar sign ($) or underscore (_).
o Do not use any Oracle Rdb reserved words.
2 – text
A text string that adds a comment to the index definition.
3 – relation-name
The name of the relation that includes the index.
4 – duplicates-clause
(B)0[mduplicates-clause =
qqq> [4mDUPLICATES[m ARE qqwqqqqqqq>qwqq> [4mALLOWED[m qq>
mq> [4mNOT[m qqj
A clause specifying whether each value of the index must be
unique. If you try to store the same value twice in an indexed
field defined as DUPLICATES NOT ALLOWED, Oracle Rdb returns an error
message and does not store or modify the record.
5 – 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
The index-storage-clause creates a storage map definition for the
index. It allows you to choose which storage area files will be
used to store index entries. You can store all index entries for
one relation in a single storage area, or you can partition the
entries over multiple storage areas.
5.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
5.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 store
clause.
If the index is a hashed index, the storage area must have a
MIXED page format.
5.1.2 – WITH_LIMIT_OF
The maximum value for the index key that will reside in the
specified storage area.
The number of literals in this clause must be the 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 define a multisegmented index using multiple keys and
use the STORE USING...WITH LIMITS clauses, 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.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.
5.2 – field-name
The name of the field that specifies what value will be used as a
limit for partitioning the index across multiple storage areas.
If the index key is multisegmented, you can include some or all
of the fields that are joined to form the index key. Separate
multiple field names with commas.
5.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.
6 – TYPE_IS
Specifies whether Oracle Rdb creates a B-tree index structure
(SORTED), or a hashed index structure (HASHED). If you specify
HASHED, you cannot choose options from the sorted-index-
param-list. Hashed indexes are effective only for exact match
retrievals. Sorted indexes are effective for range retrievals.
7 – 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
7.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.
7.2 – PERCENT-FILL
Sets the initial percentage of fullness for each node in the
index structure. The valid range is 1 to 100 percent.
7.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 the PERCENT FILL and USAGE clause is allowed
in the syntax; however, the USAGE option takes precedence over an
explicit PERCENT FILL value.
8 – field-name
The name of the field(s) that make up the index. You can create a
multisegment index by naming two or more fields, which are joined
to form the index. All of the fields must be part of the same
relation. Separate multiple field names with periods.
NOTE
If field-name refers to a field defined as VARYING STRING
data type, the size of the field must be less than or equal
to 255.
9 – ASCENDING
An optional keyword that causes Oracle Rdb to create ascending
index segments. ASCENDING is the default. To sort records in a
particular way in the result of a particular query, specify the
sort order in the SORTED BY clause.
10 – DESCENDING
An optional keyword that causes Oracle Rdb to create descending
index segments. To guarantee that records will be sorted in a
particular way in the result of a particular query, specify the
sort order in the SORTED BY clause.
11 – SIZE_IS
A compression clause that specifies that the "first n" characters
of a certain key are to be used in the index. These are specified
with the DUPLICATES ARE ALLOWED clause. For example, if you
wanted to place an index on a 100 byte field that is generally
unique to the first 20 bytes, you could specify the first 20
bytes and save as much as 80 bytes per entry.
12 – MAPPING_VALUES
A compression clause for all-numeric fields that translates
the field values into a more compactly encoded form. You can
mix mapped and unmapped fields, but the most storage space is
gained by building indexes of multiple fields of data type WORD
or LONGWORD. Oracle Rdb attempts to pack all such fields into the
smallest possible space.
The "lo-val" (low value) through "hi-val" (high value) parameters
specify the range of integers as the value of the index key.
The valid range of the compressed key:
o Cannot be zero
o The range "high-val" through "low-val" is limited to (2**31) -
4 x (10**scale)
If the value of the key is less than zero or greater than
(2**31) - 4 x (10**scale), Oracle Rdb signals an exception.
The following notes refer to compressed indexes:
All text compressed indexes require the DUPLICATES ARE ALLOWED
clause, which is the default for the DEFINE INDEX statement. If
the SIZE IS clause is specified, the field referred to by the
clause must be of the TEXT or VARYING TEXT data type. The field
must also be the same length or greater in length than the value
specified in the SIZE IS clause.
For integer field compressed indexes, the index field must be
of data type WORD or LONGWORD. You can mix mapped and unmapped
fields, but the most storage space is gained by building indexes
of multiple fields of data type WORD or LONGWORD. Oracle Rdb
attempts to pack all such fields into the smallest possible
space.
Compressed key suffixes also enable the user to use fields longer
than 254 characters as index keys.
If any data values already stored are less than "lo-val" or
greater than "hi-val", the DEFINE INDEX statement will fail.
A subsequent STORE or MODIFY operation that attempts to store a
value less than "lo-val" or greater than "hi-val" will fail.