7.14.1 Specification format and rules for index specifications

(1) Specification format

index-specification ::= /*>> {WITH INDEX (index-name)|WITHOUT INDEX} <<*/

To Page Top

(2) Explanation of specification format

WITH INDEX (index-name):

Specifies the index to be used when retrieving from the base table specified immediately before the index specification. For rules on specifying an index name, see (3) Index name specification format in 6.1.5 Qualifying a name.

WITHOUT INDEX:

Specifies that no index is to be used when retrieving from the base table specified immediately before the index specification. Instead, the table scan method is used for retrieving from the base table. For details about table scans, see About table scans in the HADB Application Development Guide.

You can check whether the index specification was applied using the access path information. For details about how to check this, see Index specification in Information displayed in the tree view in the HADB Application Development Guide.

To Page Top

(3) Rules

1. You cannot specify an index specification for a viewed table.

2. If you specify the name of a nonexistent index, the index specification is invalid.

3. If both of the following conditions are met, the index specification is invalid.

   • A B-tree index with the null-value exclusion is specified for index-name

   • A condition that contains the null value is specified in the search range of the B-tree index

4. Even when a text index is specified for index-name, the index specification is invalid if the index is determined to be unusable by HADB. For example, this is the case when you specify a LIKE predicate that cannot be evaluated for use by a text index.

5. If the index specification is invalid, HADB will automatically determine the index to be used for retrieval. For details, see B-tree indexes and text indexes used during execution of SQL statements in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide.

6. The character string enclosed in /*>> and <<*/ is not a comment. An error results if you specify something other than an index specification.

   Example

   SELECT * FROM "T1"  /*>> comment <<*/

   In the preceding example, the underlined portion is not treated as a comment. Therefore, the preceding SQL statement results in a syntax error.

7. Conversely, in the following example, the text between /* and */ is treated as a comment rather than an index specification.

   SELECT * FROM "T1" /* WITH INDEX ("IDX01") */

To Page Top

(4) Examples

The following are examples of index specifications.

The examples assume that the following indexes are defined on the employee table (EMPLOYEE):

• B-tree index BTREE_IDX defined on the column SCODE

• Text index TEXT_IDX defined on the column ADDRESS

Example 1

Retrieve data from the employee table (EMPLOYEE) using the B-tree index BTREE_IDX.

SELECT "NAME" FROM "EMPLOYEE" /*>> WITH INDEX ("BTREE_IDX") <<*/
    WHERE "SCODE" = 'S003' AND "ADDRESS" LIKE '%TOKYO%'

The underlined portion shows the index specification.

Example 2

Retrieve data from the employee table (EMPLOYEE) using the text index TEXT_IDX.

SELECT "NAME" FROM "EMPLOYEE" /*>> WITH INDEX ("TEXT_IDX") <<*/
    WHERE "SCODE" = 'S003' AND "ADDRESS" LIKE '%TOKYO%'

The underlined portion shows the index specification.

Example 3

Retrieve data from the employee table (EMPLOYEE) without using an index.

SELECT "NAME" FROM "EMPLOYEE" /*>> WITHOUT INDEX <<*/
    WHERE "SCODE" = 'S003' AND "ADDRESS" LIKE '%TOKYO%'

The underlined portion shows the index specification.

Note that if a range index is defined on the SCODE column, only the range index will still be used when the above SELECT statement is executed. For the conditions on range indexes used during retrieval, see Range indexes used during execution of SQL statements in the HADB Application Development Guide.

To Page Top