WITH INDEX Clause

Syntax

WITH INDEX index_name {keys | queryLimit | indexFilter | clientFilter}*

keys:
      KEYS keyRange [, ...]
    | KEYS ALL

keyRange:
      FIRST TO key_value
    | key_value TO LAST
    | key_value TO key_value
    | key_value

queryLimit:
    LIMIT limit_value

indexFilter:
    INDEX FILTER WHERE where_clause

clientFilter:
    CLIENT FILTER WHERE where_clause

Description

The WITH INDEX clause is used in the insert, select and delete statements. It uses the THBase index package.

  • Named parameters can be applied to any of the values above.
  • key_value is any valid string expression (cannot reference columns).

    Indicates key values for query. A key_value can be bound to a Collection of strings with a named parameter.

  • limit_value is any valid long expression (cannot reference columns).

    Indicates the maximum number of rows returned, i.e., evaluated as true, in a query.

  • where_clause is any valid boolean expression (can reference columns).

    The INDEX FILTER where_clause is evaluated on the server using the index columns, while the CLIENT FILTER where_clause is evaluated on the client using the table columns.

  • The INDEX FILTER where_clause is restricted to referencing columns declared in the CREATE INDEX.

Example

        HConnection conn = HConnectionManager.newConnection();

        conn.execute("CREATE TEMP MAPPING tab1 FOR TABLE table1"
                     + "("
                     + "keyval KEY WIDTH 15, "
                     + "f1 INCLUDE UNMAPPED ("
                     + "  val1 STRING WIDTH 10 ALIAS val1, "
                     + "  val2 INT ALIAS val5"
                     + "),  "
                     + "f2 INCLUDE UNMAPPED, "
                     + "f3 INCLUDE UNMAPPED ("
                     + "  val2 INT ALIAS val6, "
                     + "  val3 INT ALIAS val7 "
                     + "))");

        conn.execute("CREATE INDEX val1idx ON tab1 (val1) INCLUDE (val5, val6) IF NOT indexExistsForTable('val1idx', 'tab1')");

        HPreparedStatement pstmt = conn.prepareStatement("SELECT keyval, f1:val1, val5 FROM tab1 "
                                                         + "WITH INDEX val1idx KEYS FIRST TO :endkey "
                                                         + "INDEX FILTER WHERE val5 < 8 "
                                                         + "CLIENT FILTER WHERE val6 > 4");

        pstmt.setParameter("endkey", Util.getZeroPaddedNonNegativeNumber(34, 10));

        HResultSet<HRecord> records = pstmt.executeQuery();

        for (HRecord record : records)
            System.out.println("Key = " + record.getCurrentValue("keyval"));

        pstmt.close();