Index
- Thread starter rash
- Start date
RealHeavyDude
Well-Known Member
I am not 100% positive - so please bear with me. I've never thought about that problem because I never had to deal with that scenario and there is no use case I can think of where it would make sense. IMHO it is bad that progress database accepts a table without a primary index in the first place.
If no index is defined on a database table then Progress uses a so-called default index which is, AFAIK, based on the ROWID of the records. That means that, more or less, the records should be retrieved in the order they are generated but as soon as it is based on the ROWID there is no way to determine the order as records gets removed and created and ROWIDs will be re-used over time.
Heavy Regards, RealHeavyDude.
If no index is defined on a database table then Progress uses a so-called default index which is, AFAIK, based on the ROWID of the records. That means that, more or less, the records should be retrieved in the order they are generated but as soon as it is based on the ROWID there is no way to determine the order as records gets removed and created and ROWIDs will be re-used over time.
Heavy Regards, RealHeavyDude.
MaximMonin
Member
if no index is defined on temp-tables progress fetches records in order they added to temp-table. But sometimes it is not true.
TomBascom
Curmudgeon
Before we get too carried away... what do you mean by "no index defined"?
Do you mean, as RHD and Maxim are assuming, that a table has been defined in the dictionary and that no indexes were added to that table? If so, then, as RHD says, the "default" index will be used. It is based on rowids but this is not the same as the order that the records were created -- among other things rowids can skip around as creates and deletes occur. And there is no guarantee that they would be sequential in any case.
Or... do you mean that you wrote a query without coding USE-INDEX (which is good -- USE-INDEX should only be used very, very rarely when you have an excellent and testable reason for it) and you are wondering how to determine what index will be used?
If you mean the second case then you need to be aware that Progress 4GL uses a static query optimizer -- index selection is performed by examining the WHERE clause at compile-time (or when a QUERY-PREPARE takes place, which is essentially a deferred compile). The index selection rules fairly straight-forward and documented in quite a few posts. The most important rules are:
1) The greatest number of equality matches on leading components of the index.
2) Range matches can then be used.
3) After that ties are broken by a) if one of the otherwise equally desirable indexes is "primary" it wins or b) alphabetical order
It is possible for more than one index to be chosen if OR conditions are present. This sounds wonderfully useful but, in practice, it rarely is.
You can also check to see which index has been chosen by compiling with XREF and looking at the SEARCH entries.
Do you mean, as RHD and Maxim are assuming, that a table has been defined in the dictionary and that no indexes were added to that table? If so, then, as RHD says, the "default" index will be used. It is based on rowids but this is not the same as the order that the records were created -- among other things rowids can skip around as creates and deletes occur. And there is no guarantee that they would be sequential in any case.
Or... do you mean that you wrote a query without coding USE-INDEX (which is good -- USE-INDEX should only be used very, very rarely when you have an excellent and testable reason for it) and you are wondering how to determine what index will be used?
If you mean the second case then you need to be aware that Progress 4GL uses a static query optimizer -- index selection is performed by examining the WHERE clause at compile-time (or when a QUERY-PREPARE takes place, which is essentially a deferred compile). The index selection rules fairly straight-forward and documented in quite a few posts. The most important rules are:
1) The greatest number of equality matches on leading components of the index.
2) Range matches can then be used.
3) After that ties are broken by a) if one of the otherwise equally desirable indexes is "primary" it wins or b) alphabetical order
It is possible for more than one index to be chosen if OR conditions are present. This sounds wonderfully useful but, in practice, it rarely is.
You can also check to see which index has been chosen by compiling with XREF and looking at the SEARCH entries.
Similar threads
Question
ToolBox
