Changes between Initial Version and Version 1 of NonClusteredIndexes

Timestamp:

02/08/26 17:25:47 (3 weeks ago)

Author:

185022

Comment:

--

NonClusteredIndexes

@@ +1 @@
+= Некластерирани индекси
+
+Некластерираните индекси се избрани според најчестите операции:
+==== O1: Времеплов на `encounters` по пациент
+
+{{{#!sql
+select *
+from encounters
+where patient = @patient
+    and [start] >= @from and [start] < @to
+order by [start];
+go
+}}}
+
+==== O2: `observations` за еден `encounter`
+
+{{{#!sql
+select *
+from observations
+where encounter = @encounter
+order by [date];
+go
+}}}
+
+==== O3: `conditions` за еден пациент
+
+{{{#!sql
+select *
+from conditions
+where patient = @patient
+order by [start];
+go
+}}}
+
+==== O4: `medications` за пациент
+
+{{{#!sql
+select *
+from medications
+where patient = @patient
+order by [start];
+go
+}}}
+
+== `encounters`
+
+{{{#!sql
+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`
+
+{{{#!sql
+create nonclustered index IX_Observations_Encounters_Date
+on dbo.observations(encounter, [date])
+include (patient, category, code, description, value, units, type);
+go
+}}}
+
+Овој индекс ја поддржува О2 - пребарува по encounter, подредува по датум.
+
+== `conditions`
+
+{{{#!sql
+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`
+
+{{{#!sql
+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`
+
+{{{#!sql
+create nonclustered index IX_Procedures_Encounter_Start
+on dbo.procedures(encounter, [start])
+include (patient, system, code, description, base_cost, reasoncode, reasondescription);
+go
+}}}