Некластерирани индекси

Некластерираните индекси се избрани според најчестите операции:

O1: Времеплов на encounters по пациент

select *
from encounters
where patient = @patient
    and [start] >= @from and [start] < @to
order by [start];
go

O2: observations за еден encounter

select *
from observations
where encounter = @encounter
order by [date];
go

O3: conditions за еден пациент

select *
from conditions
where patient = @patient
order by [start];
go

O4: medications за пациент

select *
from medications
where patient = @patient
order by [start];
go

encounters

create nonclustered index IX_Encounters_Patient_Start
on dbo.encounters(patient, [start])
include (stop, encounterclass, code, description, payer, provider, organization, base_encounter_cost, total_claim_cost, payer_coverage);
go

Овој индекс ја поддржува О1 - бара по пациент, опсег по start.

include избегнува пребарување по типични колони.

observations

create nonclustered index IX_Observations_Encounters_Date
on dbo.observations(encounter, [date])
include (patient, category, code, description, value, units, type);
go

Овој индекс ја поддржува О2 - пребарува по encounter, подредува по датум.

conditions

create nonclustered index IX_Conditions_Patient_Start
on dbo.conditions(patient, [start])
include (stop, encounter, system, code, description);
go

create nonclustered index IX_Conditions_Encounter
on dbo.conditions(encounter)
include (patient, [start], stop, system, code, description);
go

Првиот индекс ја поддржува О3.

Вториот индекс поддржува join-ови и филтри според encounter.

medications

create nonclustered index IX_Medications_Patient_Start
on dbo.medications(patient, [start])
include (stop, payer, encounter, code, description, dispenses, base_cost, payer_coverage, totalcost, reasoncode, reasondescription);
go

create nonclustered index IX_Medications_Encounter
on dbo.medications(encounter)
include (patient, [start], stop, code, totalcost);
go

procedures

create nonclustered index IX_Procedures_Encounter_Start
on dbo.procedures(encounter, [start])
include (patient, system, code, description, base_cost, reasoncode, reasondescription);
go