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.