Versions available for this page: CUBRID 9.0.0 |
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.
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';
ISS is not applied in the following cases: