USING INDEX Clause

Description

The USING INDEX clause allows the query processor to select a proper index by specifying the index in the query.

The USING INDEX clause should be specified after the WHERE clause of the SELECT statement, the DELETE statement, and the UPDATE statement.

USING INDEX clause forces a sequential/index scan to be used or an index that can improve the performance to be included.

If the USING INDEX clause is specified with the list of index names, the query optimizer creates the optimized execution plan by calculating the query execution cost based on the specified indexes only and comparing the index scan cost and the sequential scan cost of the specified indexes. (CUBRID performs cost-based query optimization to select an execution plan).

The USING INDEX clause is useful to get the results in the desired order without 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.

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 [(+)]

• If NONE: NONE is specified, a sequential scan is selected.
• (+) : If (+) is specified at the end of the index name, the corresponding index scan is used.

Example

The following example is 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 select the index scan that uses the athlete_idx index.

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

As shown in the following query, the query optimizer calculates the index scan cost based on the indexes specified by USING INDEX when USING INDEX char_idx is specified.

When 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 forcibly specify the index scan that uses the char_idx index, specify (+) at the end of the index name as shown below.

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

USING INDEX char_idx(+);

To select the sequential scan, specify NONE on the USING INDEX clause as shown below.

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

USING INDEX NONE;

When two or more indexes have been specified in the USING INDEX clause, the query optimizer selects the proper one of the specified indexes.

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

USING INDEX char_idx, athlete_idx;

When a query is made for several tables, you can specify a table to perform index scan by using a specific index and another table to perform sequential scan. The query has the following format.

SELECT ... FROM tab1, tab2 WHERE ... USING INDEX tab1.idx1, tab2.NONE;

When executing a query with the USING INDEX clause, the query optimizer considers all available indexes on the table for which no index has been specified. For example, when the tab1 table includes idx1 and idx2 and the tab2 table includes idx3, idx4, and idx5, if indexes for only tab1 are specified but no indexes are specified for tab2, the query optimizer considers the indexes of tab2.

SELECT ... FROM tab1, tab2 WHERE ... USING INDEX tab1.idx1;

• The sequential scan of table tab1 and idx1 index scan are compared, and the optimal query plan is selected.
• The sequential scan of table tab2 and idx3, idx4, and idx5 index scan are compared, and the optimal query plan is selected.

To perform index scan for only the tab2 table and sequential scan for the tab1 table, specify tab1.NONE not to perform index scan for the tab1 table.

SELECT * from tab1,tab2 WHERE tab1.id > 2 and tab2.id < 3 USING index i_tab2_id, tab1.NONE;