Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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



Covering Index

Description

The covering index is the index including the data of all columns in the SELECT list and the WHERE, HAVING, GROUP BY, and ORDER BY clauses.

You only need to scan the index pages, as the covering index contains all the data necessary for executing a query, and it also reduces the I/O costs as it is not necessary to scan the data storage any further. To increase data search speed, you can consider creating a covering index but you should be aware that the INSERT and the DELETE processes may be slowed down due to the increase in index size.

The rules about the applicability of the covering index are as follows:

  • If the covering index is applicable, you should use the CUBRID query optimizer first.
  • For the join query, if the index includes columns of the table in the SELECT list, use this index.
  • You cannot use the covering index if an index cannot be used.
Example

CREATE TABLE t (col1 INT, col2 INT, col3 INT);

CREATE INDEX ON t (col1,col2,col3);

INSERT INTO t VALUES (1,2,3),(4,5,6),(10,8,9);

The following example shows that the index is used as a covering index because columns of both SELECT and WHERE condition exist within the index.

csql>;plan simple

SELECT * FROM t WHERE col1 < 6;

 

Query plan:

 Index scan(t t, i_t_col1_col2_col3, [(t.col1 range (min inf_lt t.col3))] (covers))

         col1         col2         col3

=======================================

            1            2            3

            4            5            6

Caution

If the covering index is applied when you get the values from the VARCHAR type column, the empty strings that follow will be truncated. If the covering index is applied to the execution of query optimization, the resulting query value will be retrieved. This is because the value will be stored in the index with the empty string being truncated.

If you don't want this, use the NO_COVERING_IDX hint, which does not use the covering index function. If you use the hint, you can get the result value from the data area rather than from the index area.

The following is a detailed example of the above situation. First, create a table with columns in VARCHAR types, and then INSERT the value with the same start character string value but the number of empty characters. Next, create an index in the column.

CREATE TABLE tab(c VARCHAR(32));

INSERT INTO tab VALUES('abcd'),('abcd    '),('abcd ');

CREATE INDEX ON tab(c);

If you must use the index (the covering index applied), the query result is as follows:

csql>;plan simple

SELECT * FROM tab where c='abcd    ' USING INDEX i_tab_c(+);

 

Query plan:

 Index scan(tab tab, i_tab_c, (tab.c='abcd    ') (covers))

 

 c

======================

'abcd'

'abcd'

'abcd'

The following is the query result when you don't use the index.

SELECT * FROM tab WHERE c='abcd    ' USING INDEX tab.NONE;

 

Query plan:

 Sequential scan(tab tab)

 

 c

======================

'abcd'

'abcd    '

'abcd '

As you can see in the above comparison result, the value in the VARCHAR type retrieved from the index will appear with the following empty string truncated when the covering index has been applied.