Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

USING INDEX Clause

Description

The USING INDEX clause forces a sequential scan or an index scan to be used or an index for better performance to be included. The USING INDEX clause must be specified after the WHERE clause of SELECT, DELETE, or UPDATE statement.

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

You can use the USING INDEX clause in the order that you want without using ORDER BY. If you do an index scan in CUBRID, the result will be created in the order of being saved in the index and you can USING INDEX to get the query result in the specific index 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, a sequential scan is selected.
  • (+) : If (+) is specified after the index name, an index scan using the specified index is selected.
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;

If you execute queries for multiple tables, you can specify to perform an index scan on one table by using a special index, and a sequential scan on other tables. These queries have the following form.

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

If you execute a query including the USING INDEX clause, the query optimizer considers all indexes available of the corresponding table for the tables not specified indexes. For example, if the table tab1 has indices idx1 and idx2, and the table tab2 has indices idx3, idx4 and idx5, specify the index for only tab1 and if if you do not specify tab2 index, the query optimizer works considering tab2 index.

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

  • Select the best query plan by comparing the sequential scan and index scan of table tab1.
  • Select the most optimized query plan by comparing the sequential scan on the table tab2 and the index scan on idx3, idx4 and idx5.

To perform an index scan on the table tab2 and a sequential scan on the table tab1, specify tab1.NONE so as not to perform an index scan on the tab1 table.

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