Select not using Btree Indexes (AREV Specific)
At 11 MAY 2004 12:39:42PM Ralph Johler wrote:
In a table with a long-standing and working Btree indexes on numeric and date fields (with some null values) typed as "INTEGER" and "DATE", the Select statement has just stopped using the indexes.
In the past we could do this:
"SELECT table WITH indexed_field BETWEEN numeric1 AND numeric2"
and get a reply in a few seconds. Now the select runs without using the indexes and takes, well hours.
If we do this now
"SELECT table WITH indexed_field EQ numeric1"
the one row is returned immediately and the Select uses the indexes.
This has also happened on the date field (typed as DATE) such that we can only use the index with
"SELECT table WITH date_field EQ 'mm/dd/yyyy'"
The LE, LT, GE, GT, =, and ] all also do not use the indexes.
We have rebuilt them and they still don't work. Any ideas?
At 11 MAY 2004 01:41PM Ralph Johler wrote:
Why the Btree indexes stopped working will doubtless remain a mystery, as will the reason(s) for the resolution working.
While just removing and rebuilding the btrees did nothing to change the situation, changing the non-working btree index setting 'Case Sensistive Index' from "No" to "Yes" makes the btree start working again.
So we back up and running. Thanks in advance to anyone working on this.