Using Indexes

Description

The USING INDEX clause enables to specify indexes in queries so that query optimizer can select appropriate indexes. The USING INDEX clause must be specified after the WHERE clause of SELECT, DELETE, and UPDATE statement.

It can forcefully enable to use the index scan or the sequential scan and include indexes improving performance.

If the list of index names is specified in the USING INDEX clause, the query optimizer calculates the query execution cost and makes the most optimized execution plan comparing cost between the index scan and the sequential scan which are specified (CUBRID performs the query optimization based on the cost of selected execution plan).

The USING INDEX is recommended to use when you want to get results in the desired order without using ORDER BY. In CUBRID, results are created by order specified in indexes. You can use USING INDEX to get the query results in the specific order when one table has multiple 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 [(+)]

• NONE: If NONE is specified, the sequential scan is used.
• (+): If (+) is specified after the index name, the index scan containing that name is used.

Example

The following example shows how to create indexes 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 using the athlete_idx index.

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

In the query shown below, if USING INDEX char_idx is specified, the query optimizer calculates index scan cost for the indexes specified.

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

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

USING INDEX char_idx;

To forcefully specify the index scan using the char_idx index, place (+) after the index name.

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

USING INDEX char_idx(+);

To make the sequential scan 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 index is specified in the USING INDEX clause as shown below, the query optimizer selects either of them.

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

USING INDEX char_idx, athlete_idx;

If you execute queries for multiple tables, you can specify to perform the index scan on one table by using a special index, and the sequential scan on other tables. The query syntax is as follows:

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

When you execute a query including the USING INDEX clause, the query optimizer will consider every index available on a table in which indexes are not specified. For example, if indexes on tab1 are specified while indexes on tab2 are not specified when tab1 has the idx1 and idx2 indexes and tab2 has the idx3, idx4, and idx5 indexes, the query optimizer will consider indexes on tab2 as well when it performs.

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

• The query optimizer will select the best query plan by comparing the sequential scan and the index scan using idx1 on the tab1 table.
• The query optimizer will select the best query plan by comparing the sequential scan and the index scan using idx3, idx4, and idx5 on the tab2 table.

To make it perform an index scan for the tab2 table only (while a sequential scan for the tab1 table), specify tab1.NONE which means than an index scan will not performed on the tab1 table.

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