Performing a range scan on a forest of trees index

While you cannot perform range scans directly on the HASH ON columns of a forest of trees index, you can perform range scans on the columns that are not listed in the HASH ON column list. To perform range scans on columns that are listed in HASH ON column list, you must create an additional B-tree index that contains the appropriate column list for the range scan.

To create indexes for range scans:

  1. Create a forest of trees index with at least one column that is not hashed.

    For example, specify:

    CREATE INDEX idx1 on tab(c1,c2) HASH ON (c1) with 100 buckets;

    You can perform a range scan directly on column c2, but not on column c1, which is listed in HASH ON column list.

  2. For range scans on the columns listed in HASH ON column list, create an additional B-tree index that contains the appropriate column list for the range scan. This additional B-tree index might have the same column list as the forest of trees index, plus or minus a column.

    For example, specify:

    CREATE INDEX idx2 on tab(c1, c2, c3);

Copyright© 2019 HCL Technologies Limited