Examine how your queries use range predicates when indexing predicate columns.
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).
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;
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’;
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’;
SELECT * FROM tab WHERE col2 >= ‘1993-01-31 00:00:00.000000’ AND col2 <= ‘1993-01-31 23:59:59.999999’;
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.