Kbase P87865: Index Consideration after changing Primary Index
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/01/2007 |
|
Status: Unverified
GOAL:
Index Consideration after changing Primary Index
GOAL:
Should I recompile my procedures if I have changed the Primary Indexes ?
GOAL:
Primary Index Change considerations
GOAL:
Recompilation of procedures after index changes
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
Since 9.0A the Index CRC was introduced. It is not anymore necessary to recompile a procedure after you add a new index or you change the primary index of the Database
This solution discuss what it's needed to be considered in case the Primary index is changed.
In the case of a Table called T1 with 4 fields F1, F2, F3 and F4, where the Primary index Unique I1 Containing F1, F2, F3
A program doing the following:
/* p1*/
FOR EACH T1 WHERE F4 = "aaa":
DISPLAY T1.
END.
The code is compiled and saved into a procedure (.r).
The index stored in the .r file is I1, because there is no index to resolve the query, the primary (I1) is used.
A new index is added, called I2 Primary Unique just containing F4 field.
The procedure is still able to run without recompiling p1.r but the index used is still I1 and it's not the most adequate, since a new primary index has been added for field F4.
The compiled procedure is using an index that is not the most adequate for resolving the query!
This situation might cause an impact in performances, if not recompiled after the Primary Index has been changed to another since there is an index that is more adequate for resolving the query.
However it must be considered that if a new index is added which helps to resolve better the static query, it is recommended to recompile the procedures in way to take advantage of the new index, otherwise this might incur in a possible performance problem.
The procedures compiled prior to the index changes will still be running using the previous indexes which were adequate at the moment the procedure was compiled against the existing indexes.
It is recommended to use Dynamic programming techniques because the Indexes used are not stored in the Compiled procedure (.r), this will take advantage of the best index to resolve the query without the need to recompile.