1  DEFINE_INDEX
   Creates an index for a relation. An index allows Oracle Rdb direct
   access to the records in the relation, to avoid sequential
   searching. You can define a sorted index or a hashed index. A
   sorted index uses the B-tree method of retrieval. A hashed index
   uses hash addressing for exact match retrievals.

   Example

     RDO>  DEFINE INDEX EMP_EMPLOYEE_ID FOR EMPLOYEES
     cont>   DUPLICATES ARE NOT ALLOWED
     cont>   TYPE IS SORTED.
     cont>     EMPLOYEE_ID.
     cont>  END EMP_EMPLOYEE_ID INDEX.
 

2  More
   To define an index for a relation using the DEFINE INDEX
   statement, you need the Oracle Rdb DEFINE privilege for the
   relation.

   When the DEFINE INDEX statement executes, Oracle Rdb adds the index
   definition to the physical database. If you have invoked the
   database with the PATHNAME specification, the definition is also
   added to the data dictionary.

   If you define a sorted index for a relation that contain no data,
   the root node for the index is not created until the first record
   is stored. When an RMU/VERIFY operation encounters a sorted index
   with no root node, it reports the index as empty.

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

   You cannot define a hashed index in a single-file database.

   If you define a hashed index, you must specify a storage map for
   it with the index-store-clause. The storage area that you specify
   must have a MIXED page format.

   An optional keyword that causes Oracle Rdb to create ascending
   or descending index segments. If you omit the ASCENDING or
   DESCENDING keywords, 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.

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

2  Format

 (B)0DEFINE_INDEX name wqqqqqqqqqqqqqqq>qqqqqqqqqqqqwq> FOR relation-name qk
                   m> DESCRIPTION IS /* text */ j                      x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
 mqqqwqqqqqqqqq>qqqqqqqqqqwqqqqwqqqqqqqqqqq>qqqqqqqqqqqqwqqqqqqqk
     m> duplicates-clause j    m> index-storage-clause qj       x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
 mqwqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk
   tq> TYPE IS qqwqqq> HASHED qqqj                           x  x
   x             mqqq> SORTED qq> sorted-index-param-list qqqu  x
   mqqqqqqqqqqqqqq> sorted-index-param-list qqqqqqqqqqqqqqqqqj  x
 lqqqqqqqqqqqqqqqqqqqqqqqqq . <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
 mwq> field-name qqwqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqwqwqqwqk
  x                x t> ASCENDING qqqqqqqqqqqqqqqqqqqqqqqu x  x x
  x                x t> DESCENDING qqqqqqqqqqqqqqqqqqqqqqu x  x x
  x                x t> SIZE IS n qqqqqqqqqqqqqqqqqqqqqqqu x  x x
  x                x m> MAPPING VALUES lo-val TO hi-val qj x  x x
  x                mqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj  x x
  mqqqqqqqqqqqqqqqqqqqqqqqqq . <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
 lqqqqqqqqqqqqqqqqqqqqqqqqqq . <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
 mq> END wqqq>qqqqw> INDEX q> .
         mq> name j

 

3  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.
 

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

3  relation-name
   The name of the relation that includes the index.
 

3  duplicates-clause

 (B)0duplicates-clause =
 
 qqq> DUPLICATES ARE qqwqqqqqqq>qwqq> ALLOWED qq>
                       mq> NOT 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.
 

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

   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.
 

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 store
   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.

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

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.
 

3  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.
 

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.
 

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 the PERCENT FILL and USAGE clause is allowed
   in the syntax; however, the USAGE option takes precedence over an
   explicit PERCENT FILL value.
 

3  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.
 

3  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.
 

3  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.
 

3  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.
 

3  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.
 

2  Examples
   Example 1

   The following example creates a simple relation index:

   DEFINE INDEX EMP_EMPLOYEE_ID FOR EMPLOYEES
     DUPLICATES ARE NOT ALLOWED.
       EMPLOYEE_ID.
    END EMP_EMPLOYEE_ID INDEX.

   This statement names the index and names the field to serve as
   the index key.

   The clause, DUPLICATES ARE NOT ALLOWED, causes Oracle Rdb to return
   an error message if a user attempts to store an identification
   number that is already assigned.

   Example 2

   The following example defines a hashed index, and uses the store
   clause to partition the index into different storage areas:

   DEFINE INDEX EMPLOYEES_HASH
     DESCRIPTION IS /* Hash index for employees */
     FOR EMPLOYEES
     DUPLICATES ARE NOT ALLOWED
     STORE USING EMPLOYEE_ID
      WITHIN
       EMPIDS_LOW WITH LIMIT OF "00200";
       EMPIDS_MID WITH LIMIT OF "00400";
       EMPIDS_OVER
     TYPE IS HASHED.
     EMPLOYEE_ID.
   END EMPLOYEES_HASH.

   Example 3

   The following example creates a multisegment index:

   DEFINE INDEX EMP_FULL_NAME FOR EMPLOYEES
      DUPLICATES ARE ALLOWED.
        LAST_NAME.
        FIRST_NAME.
        MIDDLE_INITIAL.
   END EMP_FULL_NAME INDEX.

   This statement names three fields. Oracle Rdb concatenates these
   three fields to make the multisegment index key.

   Example 4

   The following example defines the EMP_EMPLOYEE_ID index and
   causes the LAST_NAME segment to be defined in DESCENDING order.

   DEFINE INDEX EMP_FULL_NAME FOR EMPLOYEES.
   LAST_NAME DESCENDING.
   FIRST_NAME ASCENDING.
   MIDDLE_INITIAL.
   END EMP_FULL_NAME INDEX.

   Having defined such an index on a particular field does not
   guarantee that Oracle Rdb will use that index in a particular query
   retrieval. To ensure a particular sort order is returned by a
   particular query, you have to specify that order in the RSE:

   FOR E IN EMPLOYEES
       SORTED BY DESCENDING E.STATUS_CODE,
                 ASCENDING E.LAST_NAME,
                 DESCENDING E.EMPLOYEE_ID
               .
                      .
                      .

   Example 5

   The following example defines the JH_EMPLOYEE_ID index and sets
   each node size to 350 bytes and the initial fullness of each node
   to 50 percent:

   DEFINE INDEX JH_EMPLOYEE_ID FOR JOB_HISTORY
       DUPLICATES ARE ALLOWED
       NODE SIZE 350
       PERCENT FILL 50.
           EMPLOYEE_ID.
   END JH_EMPLOYEE_ID INDEX.

   Example 6

   The following example defines three new uniform storage areas
   (EMPIDS_LOW_UNIFORM, EMPIDS_MID_UNIFORM, and EMPIDS_OVER_UNIFORM)
   for the MF_PERSONNEL database. It also defines a sorted index for
   the LAST_NAME field of the EMPLOYEES relation.

   RDO> CHANGE DATABASE FILENAME MF_PERSONNEL
   cont>  DEFINE STORAGE AREA EMPIDS_LOW_UNIFORM
   cont>      FILENAME EMPIDS_LOW_UNIFORM.RDA
   cont>      END EMPIDS_LOW_UNIFORM STORAGE AREA
   cont>  DEFINE STORAGE AREA EMPIDS_MID_UNIFORM
   cont>      FILENAME EMPIDS_MID_UNIFORM.RDA
   cont>      END EMPIDS_MID_UNIFORM STORAGE AREA
   cont>  DEFINE STORAGE AREA EMPIDS_OVER_UNIFORM
   cont>      FILENAME EMPIDS_OVER_UNIFORM.RDA
   cont>      END EMPIDS_OVER_UNIFORM STORAGE AREA.
   RDO> !
   RDO> INVOKE DATABASE FILENAME MF_PERSONNEL
   RDO> !
   RDO> DEFINE INDEX EMP_FIRST_NAME_SORTED
   cont> FOR EMPLOYEES
   cont> DUPLICATES ARE ALLOWED
   cont> STORE USING FIRST_NAME
   cont>    WITHIN EMPIDS_LOW_UNIFORM WITH LIMIT OF "HOWARD"
   cont>        THRESHOLDS ARE (70,80,95);
   cont>        EMPIDS_MID_UNIFORM WITH LIMIT OF "PETER";
   cont>        EMPIDS_OVER_UNIFORM
   cont>    DEFAULT THRESHOLDS ARE (60,70,80)
   cont> TYPE IS SORTED.
   cont> FIRST_NAME.
   cont> END EMP_FIRST_NAME_SORTED INDEX.

   The EMP_FIRST_NAME_SORTED index stores index entries into one of
   the three storage areas based on the values stored in the LAST_
   NAME field.

   Note that the EMPIDS_LOW_UNIFORM storage area receives the
   threshold values of (70,80,95) specified with the THRESHOLDS
   clause, and the EMPIDS_MID_UNIFORM and EMPIDS_OVER_UNIFORM
   storage areas receive the default threshold values of (60,70,80)
   specified with the DEFAULT THRESHOLDS clause.

   Example 7

   To create a compressed index for fields that use SIGNED BYTE,
   SIGNED WORD, and SIGNED LONGWORD data types, use the MAPPING
   VALUES clause of the DEFINE INDEX statement for the field or
   fields being indexed, as shown in the examples. You can use the
   DUPLICATES ARE NOT ALLOWED clause with an integer compressed
   index. In the examples, PRODUCT_ID, YEAR_NUMBER, and PRODUCT_
   DESCR are the three fields that are defined with the DUPLICATES
   ARE NOT ALLOWED clause.

   DEFINE INDEX PS_DATE_2 FOR PRODUCT_SCHEDULE
   DUPLICATES ARE NOT ALLOWED.
     PRODUCT_ID.
     YEAR_NUMBER         MAPPING VALUES 1970 to 2070.
     PRODUCT_DESCR       SIZE IS 20.
   END.

   Note that you can mix mapped and unmapped fields, but the most
   storage space is gained by building indexes of multiple fields of
   data type of SIGNED WORD or SIGNED LONGWORD. Oracle Rdb attempts to
   pack all such fields into the smallest possible space.

   Example 8

   The following examples define several text and integer compressed
   indexes.

   !
   ! Define the integer compressed index PORT_NUM_CINDEX on the PORT_NUM
   ! field of the PORT relation:
   !
   DEFINE INDEX PORT_NUM_CINDEX FOR PORT DUPLICATES NOT ALLOWED.
   PORT_NUM MAPPING VALUES 90 TO 1000.
   END.
   !
   ! Define the text compressed index PORT_COUNTRY_CITY_INDEX on the
   ! COUNTRY and CITY fields of the PORT relation:
   !
   DEFINE INDEX PORT_COUNTRY_CITY_CINDEX FOR PORT.
   COUNTRY SIZE 10.
   CITY SIZE 5.
   END.
   !
   ! Produce a list of ports sorted by COUNTRY and CITY name from
   ! the PORT relation:
   !
   FOR P IN PORT SORTED BY P.COUNTRY, P.CITY PRINT P.* END_FOR
   !
   ! Define the integer compressed index CHANNEL_DEPTH_CINDEX on the
   ! CHANNEL_DEPTH field of the PORT relation:
   !
   DEFINE INDEX CHANNEL_DEPTH_CINDEX FOR PORT.
   CHANNEL_DEPTH MAPPING VALUES 20 TO 100.
   END.
   !
   ! Define the integer compressed index DISTANCE_CINDEX on the
   ! DISTANCE field of the PORT relation:
   !
   DEFINE INDEX DISTANCE_CINDEX FOR PORT.
   DISTANCE MAPPING VALUES 0 TO 30000.
   END.
   !
   ! Define the integer compressed index TRANS_COST_CINDEX on the
   ! TRANS_COST field of the PORT relation:
   !
   DEFINE INDEX TRANS_COST_CINDEX FOR PORT.
   TRANS_COST MAPPING VALUES 0 TO 10000.
   END.
   !
   ! Define the integer compressed index PNUM_CDEP_DIST_TCOST_CINDEX
   ! on the PORT_NUM, CHANNEL_DEPTH, DISTANCE, and TRANS_COST fields
   ! of the PORT relation:
   !
   DEFINE INDEX PNUM_CDEP_DIST_TCOST_CINDEX FOR PORT
   DUPLICATES NOT ALLOWED.
   PORT_NUM MAPPING VALUES 100 TO 1000.
   CHANNEL_DEPTH MAPPING VALUES 20 TO 100.
   DISTANCE MAPPING VALUES 0 TO 30000.
   TRANS_COST MAPPING VALUES 0 TO 10000.
   END.
   !
   ! Define the CHANNEL_DEPTH_DESC_CINDEX index on the CHANNEL_DEPTH
   ! field of the PORT relation. The DESCENDING keyword causes descending
   ! index segments to be created for the CHANNEL_DEPTH field.
   !
   DEFINE INDEX CHANNEL_DEPTH_DESC_CINDEX FOR PORT.
   CHANNEL_DEPTH DESCENDING MAPPING VALUES 20 TO 100.
   END.