Xref Table -- How to Index?


Xref Table -- How to Index?

I have a table that resolves the M-M relationship between my member and postal address tables. Member table has UPI on mbr_key. Postal Address has UPI on postal_addr_key. What's the best indexing scheme for the xref? Right now it has a NUPI on mbr_key. Would a composite on mbr_key + postal_addr_key help the join to BOTH parent tables? I think probably no, but what is the best? Most queries will need all 3 tables and will be driven from the member side (wanting to find one of the addresses associated with the members identified in the member filter condition(s)).

And to add another twist, most of the time there will be an outer join from member to xref.

Re: Xref Table -- How to Index?

I don't see why the general rule of setting the PI to support the primary access path would be any different. In any case, setting the PI to both PIs (a composite key) in the Assn table is usually the worst thing to do. The purpose of an Assn table is to get from one side of the M:M to the other side. You would never know both keys.