Efficient Use of Indexes

---

Several improvements have been made in this release involving the use of indexes to optimize on query characteristics other than the Restriction, such as a DISTINCT or ORDER BY clause. In some cases, indexes were already being used for optimization purposes but are used more efficiently in this release.

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 

Past Behavior

Pervasive.SQL 2000i does not support this type of optimization. Pervasive.SQL 2000i retrieves every row that satisfies the restriction and then rejects the rows with duplicate values.

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 

Past Behavior

Pervasive.SQL 2000i does not support this type of optimization. Pervasive.SQL 2000i retrieves every row that satisfies the restriction and then rejects the rows with duplicate values.

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 

Past Behavior

Pervasive.SQL 2000i does not fully support this type of optimization.

Pervasive.SQL 2000i uses an index to optimize on a DISTINCT preceding a selection-list or a GROUP BY only if the columns appear in the selection-list or GROUP BY clause in the same order as they appear as columns in the index.

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 

Past Behavior

Pervasive.SQL 2000i does not fully support this type of optimization.

Pervasive.SQL 2000i can only use an index if all the segments are ascending.

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 SRDE 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 

Past Behavior

Pervasive.SQL 2000i does not have search optimization. The same session (client ID) is used for both the search and UPDATE. The updates are seen immediately by the search. Therefore, the SRDE would not optimize on the index but scan the table instead.