Queries with Range Predicates

Examine how your queries use range predicates when indexing predicate columns.

In the following cases with range predicates, a DATE, TIME, or DTTM index is chosen to process the queries:

Compare condition:
```
SELECT * FROM tab WHERE col1 < ‘2002/10/09’;
SELECT * FROM tab WHERE col2 >= ‘2002/01/01 09:12:04.006’;
```
One side of the comparison operator is a column name and the other side is a constant expression (constant value or host variable).

Between condition:

SELECT * FROM tab WHERE col3 BETWEEN ‘09:12:04.006’ AND ‘20:12:04.006’;
SELECT * FROM tab WHERE col2 BETWEEN tmp_datetime1 AND tmp_datetime2;

For these types of queries, a DATE, TIME, or DTTM index is usually faster than a HNG index.

In three specific cases, use of the DATE or DTTM index may significantly improve performance:

The range of the predicate is exactly one or more years (the actual start date is the beginning of a year and the actual end date is the end of a year). For example:
```
SELECT * FROM tab WHERE col1 BETWEEN ‘1993-01-01’ AND ‘1996-12-31’;

SELECT * FROM tab WHERE col1 >= ‘1993-01-01’ AND
col1 < ‘1997-01-01’;

SELECT * FROM tab WHERE col2 BETWEEN ‘1993-01-01 00:00:00.000000’ AND ‘1996-12-31 23:59:59.999999’;
```
The range of the predicate is exactly one or more months in the same year (the actual start date is the beginning of a month and the actual end date is the end of a month). For example,
```
SELECT * FROM tab WHERE col1 > ‘1993-01-31’ AND
col1 <= ‘1993-06-31’;

SELECT * FROM tab WHERE col2 >= ‘1993-01-01 00:00:00.000000’ AND col1 < ‘1993-06-01 00:00:00.000000’;
```
The range of the predicate is exactly one day. For example,
```
SELECT * FROM tab WHERE col2 >= ‘1993-01-31 00:00:00.000000’ AND
col2 <= ‘1993-01-31 23:59:59.999999’;
```

Note: In the three cases above, you must be careful about the concepts of range of years, range of months, and exactly one day. For example, there are four cases for a DTTM index that are recognized as range of years:

col2 >  ’year1/12/31 23:59:59.999999’ and
col2 <  ’year2/01/01 00:00:00.000000’

col2 >= ’year1/01/01 00:00:00.000000’ and
col2 <  ’year2/01/01 00:00:00.000000’

col2 >  ’year1/12/31 23:59:59.999999’ and
col2 <= ’year2/12/31 23:59:59.999999’

col2 >= ’year1/01/01 00:00:00.000000’ and
col2 <= ’year2/12/31 23:59:59.999999’

Ranges as in the following examples do not match range of years:

col2 > ’year1/12/31 23:59:59.999999’ and
col2 <= ’year2/01/01 00:00:00.000000’

col2 > ’year1/01/01 00:00:00.000000’ and
col2 <  ’year2/01/01 00:00:00.000000’

The first range does not match, because it includes the value 'year2/01/01 00:00:00:000000' in addition to the range of years. The second range loses the value 'year1/01/01 00:00:00.000000.'

Similar specifics apply to range of months, and exactly one day, for both DTTM and DATE indexes.

If a small date range (less than 60 values) does not fit the three specific cases above, then LF and HG indexes are faster than the DATE index.