Can anyone help me with index usage issues I am have, using ODBC connection to Progress 9.1D. I'm quite new to progress, but have some SQL experience. Anyways, I can't seem to use an index with any operator other than "=" in the Where clause
for example if an Index "myindex" exists on "myTable" on Col1(varchar), Col2(integer) and Col3(date)
SELECT *
FROM myTable
WHERE Col1 = 'Col1value'
AND Col2 = 0
AND Col3 = {fn CURDATE()}
seems to work just fine, however if I change my equals to >=, <>, BETWEEN or even NOT(Col2 = 0) it appears from performance, that the query does a table scan.
SELECT *
FROM myTable
WHERE Col1 = 'Col1value'
AND Col2 >= 0
AND Col3 = {fn CURDATE()}
The examples above are very simplified but I've tried several variations and little tricks but haven't been successful, and if needed I can post some of the other things I've tried. If anyone has found a setting I can change or workaround please let me know. Also most of the tables that I am tring this on have a few hundred thousand records, or several million. Thanks.
for example if an Index "myindex" exists on "myTable" on Col1(varchar), Col2(integer) and Col3(date)
SELECT *
FROM myTable
WHERE Col1 = 'Col1value'
AND Col2 = 0
AND Col3 = {fn CURDATE()}
seems to work just fine, however if I change my equals to >=, <>, BETWEEN or even NOT(Col2 = 0) it appears from performance, that the query does a table scan.
SELECT *
FROM myTable
WHERE Col1 = 'Col1value'
AND Col2 >= 0
AND Col3 = {fn CURDATE()}
The examples above are very simplified but I've tried several variations and little tricks but haven't been successful, and if needed I can post some of the other things I've tried. If anyone has found a setting I can change or workaround please let me know. Also most of the tables that I am tring this on have a few hundred thousand records, or several million. Thanks.