Efficient Use of Indexes
Indexes can optimize on query characteristics other than the Restriction, such as a DISTINCT or ORDER BY clause.
For definitions of the technical terms used in this chapter, see Terminology.
DISTINCT in Aggregate Functions
An index can be used to reduce the number of rows retrieved for queries with a selection-list that consists of an Aggregate Function containing the DISTINCT keyword. To be eligible for this type of optimization, the expression on which the DISTINCT keyword operates must consist of a single column reference. Furthermore, the column must be the leading segment of an index.
For example, suppose an index exists on table t1 with the first segment on column c1. The index can be used to avoid retrieving rows with duplicate values of column c1:
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = 1
DISTINCT Preceding Selection-list
An index can be used to reduce the number of rows retrieved for some queries with the DISTINCT keyword preceding the selection-list. To be eligible for this type of optimization, the selection-list must consist only of column references (no complex expressions such as arithmetic expressions or scalar functions), and the referenced columns must be the leading segments of a single index.
For example, suppose an index exists on table t1 with the first three segments on columns c1, c2 and c3, in any order. The index can be used to avoid retrieving rows with duplicate values for the selection-list items:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c2 = 1
Relaxed Index Segment Order Sensitivity
Whether an index can be used to optimize on an ORDER BY clause depends on the order in which the columns appear as segments in the index. Specifically, to be eligible for this type of optimization, the columns in the ORDER BY clause must make up the leading segments of an index, and the columns must appear in the ORDER BY clause in the same order as they appear as segments in the index.
In contrast, an index can be used to optimize on a DISTINCT preceding a selection-list or on a GROUP BY clause as long as the selection-list or GROUP BY clause consists of columns that are the leading segments of the index. This statement is true regardless of the order in which the columns appear as segments in the index.
For example, suppose an index exists on table t1 with the first three segments on columns c1, c2 and c3, in any order. The index can be used to optimize on the DISTINCT in the following queries:
SELECT DISTINCT c1, c2, c3 FROM t1
SELECT DISTINCT c2, c3, c1 FROM t1 WHERE c3 > 1
The index can be used to optimize on the GROUP BY in the following queries:
SELECT c1, c2, c3, count(*) FROM t1 GROUP BY c2, c1, c3
SELECT c2, c3, c1, count(*) FROM t1 GROUP BY c3, c2, c1
For the index to be used to optimize on the ORDER BY, however, the index segments must be in the order of c2, c1, c3:
SELECT c1, c2, c3 FROM t1 ORDER BY c2, c1, c3
Relaxed Segment Ascending Attribute Sensitivity
Whether an index can be used to optimize on an ORDER BY clause depends on several conditions.
Specifically, an index can be used for optimization of ORDER BY if all of the following conditions are satisfied:
•
the DESC keyword follows the column in the ORDER BY clause
•
the corresponding index segment is defined as descending
•
the specified column is not nullable.
In addition, an index can be used for optimization of ORDER BY if all of the following converse conditions are satisfied (note that nullable columns are allowed for ascending ORDER BY):
•
the ASC keyword or neither ASC nor DESC follows the column in the ORDER BY statement
•
the corresponding index segment is defined as ascending.
As well, an index can be used for optimization of ORDER BY if the ascending/descending attributes of all the involved segments are the exact opposite of each ASC or DESC keyword specified in the ORDER BY. Again, the segments defined as descending can only be used if the associated columns are not nullable.
Indexes can be used for any of the restriction optimizations, optimization on a DISTINCT, or optimization on a GROUP BY clause, regardless of the ascending/descending attribute of any of the segments.
For example, suppose an index exists on table t1 with the first two segments on columns c1 and c2, in that order, and both segments are ascending. The index can be used to optimize on the following queries:
SELECT c1, c2, c3 FROM t1 ORDER BY c1, c2
SELECT c1, c2, c3 FROM t1 ORDER BY c1 DESC, c2 DESC
SELECT DISTINCT c1, c2 FROM t1
SELECT DISTINCT c2, c1 FROM t1
SELECT * FROM t1 WHERE c1 = 1
Suppose an index exists on table t1 with the first two segments on columns c1 and c2, in that order, with the segment on c1 defined as ascending and the segment on c2 defined as descending. Suppose also that c2 is nullable. The second segment cannot be used to optimize on ORDER BY because the column is both descending and nullable. The index can be used to optimize on the following queries:
SELECT c1, c2, c3 FROM t1 ORDER BY c1
SELECT c1, c2, c3 FROM t1 ORDER BY c1 DESC
SELECT DISTINCT c1, c2 FROM t1
SELECT DISTINCT c2, c1 FROM t1
SELECT * FROM t1 WHERE c1 = 1
If column c2 is not nullable, then the index can also be used to optimize on the following queries:
SELECT c1, c2, c3 FROM t1 ORDER BY c1, c2 DESC
SELECT c1, c2, c3 FROM t1 ORDER BY c1 DESC, c2
Search Update Optimization
You may take advantage of search optimization when you update a leading segment index by using the same index in the WHERE clause for the search. The database engine uses one session (client ID) for the UPDATE and another session for the search.
The following statements benefit from search optimization.
CREATE TABLE t1 (c1 INT)
CREATE INDEX t1_c1 ON t1(c1)
INSERT INTO t1 VALUES(1)
INSERT INTO t1 VALUES(1)
INSERT INTO t1 VALUES(9)
INSERT INTO t1 VALUES(10)
INSERT INTO t1 VALUES(10)
UPDATE t1 SET c1 = 2 WHERE c1 = 10
UPDATE t1 SET c1 = c1 + 1 WHERE c1 >= 1