9474d0f8
From: tom7744.no.spam@cox.net
On Sat, 8 Aug 2009 20:50:45 -0700 (PDT), Dave
wrote:
You may be misunderstanding. A duplicate key is not unique.
-Tom.
Microsoft Access MVP
>On Aug 8, 7:59 pm, Tom van Stiphout wrote:
>> 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
>
>i cannot add a duplicate index on ABC.fieldID2 as it would not be
>unique. (composite keys need all their parts (fields) to make rows
>unique) i may be misunderstanding you, if so sorry about that.
>
>thanks, dave
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
