99fa0cae   
   From: tom7744.no.spam@cox.net   
      
   On Sat, 8 Aug 2009 18:27:15 -0700 (PDT), Dave    
   wrote:   
      
   Put a duplicate index on ABC.fieldID2.   
      
   -Tom.   
   Microsoft Access MVP   
      
      
   >At work I uncovered a join that (foreign key) references only part of   
   >composite primary key on another table.  the data in the composite PK   
   >table have over 200 M rows - so the ensuing table scanning is killing   
   >the performance.  i'd like to know some options for best addressing   
   >how to fix this.   
   >   
   >E.g. table ABC (composite PK: fieldID1, fieldID2).   
   >table DEF (PK: fieldID3) - references only fieldID1.   
   >table MNO (PK: fieldID4) - references only fieldID2.   
   >i.e. table DEF only has access to fieldID1, and MNO only has access to   
   >fieldID2.   
   >   
   >some options that i'm not very fond of are:   
   >- adding fieldID2 to table DEF, and fieldID1 to table MNO.   
   >- OR creating a relation tables between ABC, and DEF with fields:   
   >fieldID3, fieldID1, fieldID2, also with composite PK for RI (and   
   >similar relation table for ABC to MNO)   
   >- replacing the composite key PK with single valued identity value   
   >does not seem like a good plan either, as the data lends itself very   
   >well to having a composite key PK.   
   >   
   >let me know if more details are needed, and thanks for any advice   
   >offered, dave   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)