Abe Froman
Member
I have been finding old indices that are unique in practice but aren't set as unique. I'd like to know if there will be any performance gain or any other good reason to take the time to make these indices unique.
BTW, using Progress 8.
Here are some examples:
po_mstr
po_nbr_key (pu)
(1) po_nbr
po_type_key (u)
(2) po_type, po_nbr
po_vend_key (-)
(2) po_vend, po_nbr
po_buyer_key (-)
(3) po_buyer, po_vend, po_nbr
In this case, po_vend_key and po_buyer_key are unique only due to the field po_nbr. Should I make them unique? Would I save space and streamline the indexing process if I shortened every index down to one field and made all but the primary non-unique? What would I gain or lose in each case?
Here's a different case.
xxse
xxse_key (pua)
(3) xxse_mstr_ord_nbr, xxse_mstr_ord_sect, xxse_ord_seq_id
xxse_sonbr_det_key (-)
(2) xxse_so_nbr, xxse_det_line
xxse_sonbr_sod_key (-)
(2) xxse_so_nbr, xxse_sod_line
xxse_so_nbr_key (-)
(2) xxse_so_nbr, xxse_ord_seq_id
All the indices with 2 fields are unique in practice, but not actually labelled as unique. I am aware of validation bonuses to making them unique, but other validation is already in place. Are there any other gains? I would guess they'll be slightly faster and take up slightly more space? If so, what real benefit is gained by 'fixing' these indices?
Are there any big-picture ideas I can learn here? If an ancient index structure can be slightly improved, is it always worth it to make the change? Should I spend more effort finding places to shorten and join indices instead of making multiple ones unique?
BTW, using Progress 8.
Here are some examples:
po_mstr
po_nbr_key (pu)
(1) po_nbr
po_type_key (u)
(2) po_type, po_nbr
po_vend_key (-)
(2) po_vend, po_nbr
po_buyer_key (-)
(3) po_buyer, po_vend, po_nbr
In this case, po_vend_key and po_buyer_key are unique only due to the field po_nbr. Should I make them unique? Would I save space and streamline the indexing process if I shortened every index down to one field and made all but the primary non-unique? What would I gain or lose in each case?
Here's a different case.
xxse
xxse_key (pua)
(3) xxse_mstr_ord_nbr, xxse_mstr_ord_sect, xxse_ord_seq_id
xxse_sonbr_det_key (-)
(2) xxse_so_nbr, xxse_det_line
xxse_sonbr_sod_key (-)
(2) xxse_so_nbr, xxse_sod_line
xxse_so_nbr_key (-)
(2) xxse_so_nbr, xxse_ord_seq_id
All the indices with 2 fields are unique in practice, but not actually labelled as unique. I am aware of validation bonuses to making them unique, but other validation is already in place. Are there any other gains? I would guess they'll be slightly faster and take up slightly more space? If so, what real benefit is gained by 'fixing' these indices?
Are there any big-picture ideas I can learn here? If an ancient index structure can be slightly improved, is it always worth it to make the change? Should I spend more effort finding places to shorten and join indices instead of making multiple ones unique?