Kbase P55315: Is it a good idea to index records on logical values?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/12/2010 |
|
Status: Verified
GOAL:
Is it a good idea to index records on logical values?
GOAL:
Avoiding indexes on logical values
GOAL:
What is the impact of creating an index based on a logical field?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
OpenEdge Category: Language (4GL/ABL)
FIX:
If your application needs to identify records that satisfy some Boolean condition; Such as Active vs. Inactive or Male vs. Female or Domestic vs. Foreign, it is not a good idea to do this by means of indexes on Logical fields that represent the two conditions.
The same is true of other fields that have only a handful of values, whether they are character values such as "Foreign" and "Domestic" or integer values representing those meanings.
An index bracket is the portion of the index that the OpenEdge RDBMS must search through to identify all the records that match your selection criteria. If this is half or a large fraction of all the records, then the index is not serving its purpose and data retrieval will not be efficient.
Instead, you should consider encoding these kinds of values in a word-indexed character field, as discussed earlier. Under very special circumstances it might be beneficial to define an index on a logical value when, for example, 99 percent of the records are True for that value and you frequently need to identify the one percent that are False.