Here are 5 rules that will definitely help you to decide which indexes to create!
Rules number #1
If your record retrievals are based on one field at a time, for example dept = 'A112' , create an index on these fields.
Rules number #2
If your record retrievals are based on a combination of fields, look at the combinations.
Rules number #3
If the comparison operator is OR (for example, DEPT = 'A112' OR HIRE_DATE > {07/03/94}), an index does not help performance. Therefore, you need not create one.
If the comparison operator is OR (for example, DEPT = 'A112' OR HIRE_DATE > {07/03/94}), an index does not help performance. Therefore, you need not create one.
Rules number #4
If the retrieval conditions contain both AND and OR comparison operators, you can use an index if the OR conditions are grouped.
If the retrieval conditions contain both AND and OR comparison operators, you can use an index if the OR conditions are grouped.
Example:
dept = 'A112' AND (hire_date > {07/03/94} OR exempt = 1) In this case, an index on the DEPT field improves performance.
Rules number #5
If the AND conditions are grouped, an index does not improve performance.
If the AND conditions are grouped, an index does not improve performance.
Example:
(dept = 'A112' AND hire_date) > {07/03/94}) OR exempt = 1
EmoticonEmoticon