Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 9.0.0 | 

Index Skip Scan

Description

Index Skip Scan (also known as ISS) is an optimization method that allows ignoring the first column of an index when the first column of the index is not included in the condition but the following column is included in the condition (in most cases, =).

Generally, ISS should consider several columns (C1, C2, ..., Cn). Here, a query has the conditions for the consecutive columns and the conditions are started from the second column (C2) of the index.

INDEX (C1, C2, ..., Cn);

 

SELECT ... WHERE C2 = x and C3 = y and ... and Cp = z; -- p <= n

 

SELECT ... WHERE C2 < x and C3 >= y and ... and Cp BETWEEN (z and w); -- other conditions than equal

The query optimizer eventually determines whether ISS is the most optimum access method based on the cost. ISS is applied under very specific situations, such as when the first column of an index has a very small number of DISTINCT values compared to the number of records. In addition, ISS should provide higher performance compared to Index Full Scan. For example, when the first column of index columns has very low cardinality, such as the value of men/women or hundreds of thousands of records with the value of 1~100, it may be inefficient to perform index scan by using the first column value. So ISS is useful in this case.

ISS skips reading most of the index pages in the disk and uses range search which is dynamically readjusted. Generally, ISS can be applied to a specific scenario when the number of DISTINCT values in the first column is very small. If ISS is applied to this case, ISS provides significantly higher performance than the index full scan.

Example

CREATE TABLE t (name string, gender char (1), birthday datetime);

 

CREATE INDEX idx_t_gen_name on t (gender, name);

-- Note that gender can only have 2 values, 'M' and 'F' (low cardinality)

 

-- this would qualify to use Index Skip Scanning:

SELECT * FROM t WHERE name = 'SMITH';

Note

ISS is not applied in the following cases:

  • Filtered index
  • The first column of an index is a range filter or key filter
  • Hierarchical query
  • Aggregate function included