Adam said:

>If you have a foreign key constraint on

LINK_MEDICALFACILITY_PATIENT.MEDICALFACILITY_ID, than the underlying
index will be used. If your database contains very few MEDICALFACILITY
records, this index may be worthless or even counter productive.

The table LINK_MEDICALFACILITY_PATIENT.MEDICALFACILITY_ID has three
fields. LINK_ID, MEDICALFACILITY_ID and PATIENT_ID. The latter two
have foreign key constraints and the first is the PK.

The field MEDICALFACILITY_ID has many duplicates. So, I created a new
index on this table:
CREATE UNIQUE INDEX IDX_LINK_MED_PAT ON
LINK_MEDICALFACILITY_PATIENT(MEDICALFACILITY_ID,LINK_ID);
I backed up and restored the Db. Then, I ran the SP for
MEDICALFACILITY_ID = 1 under 3 scenarios
500/100000 records have MEDICALFACILITY_ID = 1
50000/100000 records have MEDICALFACILITY_ID = 1
90000/100000 records have MEDICALFACILITY_ID = 1
In all cases, using the new index, or not using it makes no
difference. The plan is:
PLAN MERGE (SORT (LINK_MEDICALFACILITY_PATIENT INDEX
(IDX_LINK_MEDIC_PAT)), SORT (JOIN (ENTY_PERSON INDEX
(I_ENTY_PERSON_LASTNAME), ENTY_PATIENT INDEX (I_ENTY_PATIENT_ENTITY_ID))))
Why does this index not make any difference? TIA