USING INDEX Clause

Description

The USING INDEX clause allows indexes to be specified in the query so that the query processor can choose an appropriate index.

The USING INDEX clause must be specified after the WHERE clause in the SELECT, DELETE or UPDATE statement.

Syntax

SELECT . . . FROM . . . WHERE . . .

[USING INDEX { NONE | index_spec [ {, index_spec } ...] } ] [ ; ]

DELETE FROM . . . WHERE . . .

[USING INDEX { NONE | index_spec [ {, index_spec } ...] } ] [ ; ]

UPDATE . . . SET . . . WHERE . . .

[USING INDEX { NONE | index_spec [ {, index_spec } ...] } ] [ ; ] 

index_spec :

 [table_name.]index_name [(+)]

• NONE : If NONE is specified, a sequential scan is selected.
• (+) : If (+) is specified after the index name, an index scan using the specified index is selected.

The USING INDEX clause forces a sequential/index scan to be used or an index that does not degrade the performance to be included.

If a list of index names is specified in the USING INDEX clause, the query optimizer calculates the query execution cost only for the specified index, and then creates an optimized execution plan by comparing the index scan cost of the listed indexes and the sequential scan cost (CUBRID performs query optimization based on the cost in choosing the execution plan).

USING INDEX can be useful when you want to get the result in the desired order without using ORDER BY. When index scan is performed by CUBRID, the results are created in the order they were saved in the index. When there are more than one indexes in one table, you can use USING INDEX to get the query results in a given order of indexes.

Example

The following is an example of creating an index based on the table creation statement of the athlete table.

CREATE TABLE athlete (
   code             SMALLINT    NOT NULL PRIMARY KEY,
   name             VARCHAR(40) NOT NULL,
   gender           CHAR(1)     ,
   nation_code      CHAR(3)     ,
   event            VARCHAR(30) ,
   );
CREATE UNIQUE INDEX athlete_idx ON athlete(code, nation_code);
CREATE INDEX char_idx ON athlete(gender, nation_code);

For the following query, the query optimizer can choose an index scan that uses the athlete_idx index.

SELECT * FROM athlete WHERE gender='M' AND nation_code='USA';

As in the query below, if USING INDEX char_idx is specified, the query optimizer calculates the index scan cost only for the given index specified by USING INDEX.

If the index scan cost is less than the sequential scan cost, an index scan is performed.

SELECT * FROM athlete WHERE gender='M' AND nation_code='USA'
USING INDEX char_idx(+);

To forcefully specify an index scan that uses the char_idx index, place (+) after the index name.

SELECT * FROM athlete WHERE gender='M' AND nation_code='USA'
USING INDEX char_idx(+);

To allow a sequential scan to be selected, specify NONE in the USING INDEX clause as follows:

SELECT * FROM athlete WHERE gender='M' AND nation_code='USA'
USING INDEX NONE;

If more than one indexes were specified in the USING INDEX clause as shown below, the query optimizer chooses an appropriate one from the specified indexes.

SELECT * FROM athlete WHERE gender='M' AND nation_code='USA'
USING INDEX char_idx, athlete_idx;