Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/i2b2_facts_index.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 6.5 KB
Line 
1/** i2b2_facts_index.sql -- restore indexes on i2b2 fact table
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7This is invoked from i2b2_deid.py, which provides &&star and &&index_opts.
8
9These indexes are originally from
10i2b2demodata/edu.harvard.i2b2.data/Release_1-4/NewInstall/Demodata/scripts/crc_create_datamart_oracle.sql
11*/
12
13whenever sqlerror continue;
14alter table "&&star".observation_fact
15  drop constraint observation_fact_pk;
16drop index "&&star".observation_fact_pk; -- obsolete?
17whenever sqlerror exit;
18
19
20/* ack: http://marcel.vandewaters.nl/oracle/database-oracle/constraint-exceptions */
21whenever sqlerror continue;
22drop table "&&star".EXCEPTIONS;
23whenever sqlerror exit;
24
25CREATE TABLE "&&star".EXCEPTIONS (ROW_ID UROWID,
26                         OWNER VARCHAR2(30),
27                         TABLE_NAME VARCHAR2(30),
28                         CONSTRAINT_NAME VARCHAR2(30));
29
30alter table "&&star".observation_fact
31  add constraint observation_fact_pk
32  unique(ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD,INSTANCE_NUM)
33  exceptions into "&&star".exceptions nologging;
34
35
36/* Default i2b2 indexes (see #1369, #845, #2946) */
37whenever sqlerror continue;
38drop index "&&star".FACT_NOLOB;
39drop index "&&star".FACT_PATCON_DATE_PRVD_IDX;
40drop index "&&star".FACT_CNPT_PAT_ENCT_IDX;
41drop index "&&star".FACT_CNPT_IDX;
42whenever sqlerror exit;
43
44
45/* See #1536 and #1537- we found that repeating the procedure for using bitmap
46AND b-tree indexes (as per #1369) did not work.  Building table stats never
47finished after 2.6 days.  Limiting the stats percentage allowed them to finish
48but resulted in poor counting query performance again.  So, commenting out these
49indexes (leaving for reference).
50CREATE INDEX &&star.FACT_NOLOB ON &&star.OBSERVATION_FACT
51(
52        PATIENT_NUM,
53        START_DATE,
54        CONCEPT_CD,
55        ENCOUNTER_NUM,
56        INSTANCE_NUM,
57        NVAL_NUM,
58        TVAL_CHAR,
59        VALTYPE_CD,
60        MODIFIER_CD,
61        VALUEFLAG_CD,
62        PROVIDER_ID,
63        QUANTITY_NUM,
64        UNITS_CD,
65        END_DATE,
66        LOCATION_CD,
67        CONFIDENCE_NUM,
68        UPDATE_DATE,
69        DOWNLOAD_DATE,
70        IMPORT_DATE,
71        SOURCESYSTEM_CD,
72        UPLOAD_ID
73)&&index_opts;
74
75
76CREATE INDEX &&star.FACT_CNPT_PAT_ENCT_IDX ON &&star.OBSERVATION_FACT
77(
78  CONCEPT_CD,
79  INSTANCE_NUM,
80  PATIENT_NUM,
81  ENCOUNTER_NUM
82) &&index_opts;
83
84
85CREATE INDEX &&star.FACT_PATCON_DATE_PRVD_IDX ON &&star.OBSERVATION_FACT
86(
87  PATIENT_NUM,
88  CONCEPT_CD,
89  START_DATE,
90  END_DATE,
91  ENCOUNTER_NUM,
92  INSTANCE_NUM,
93  PROVIDER_ID,
94  NVAL_NUM,
95  VALTYPE_CD
96) &&index_opts;
97*/
98
99
100/* As per suggestion from Peter Beninato at OHSU (#1069) we added the bitmap
101indexes below. */
102whenever sqlerror continue;
103drop index "&&star".OBS_FACT_ENC_NUM_BI;
104whenever sqlerror exit;
105
106create bitmap index &&star.OBS_FACT_ENC_NUM_BI
107 on &&star.observation_fact (ENCOUNTER_NUM)
108 &&index_opts;
109
110
111whenever sqlerror continue;
112drop index "&&star".OBS_FACT_PAT_NUM_BI;
113whenever sqlerror exit;
114
115create bitmap index &&star.OBS_FACT_PAT_NUM_BI
116 on &&star.observation_fact (PATIENT_NUM)
117 &&index_opts;
118
119
120whenever sqlerror continue;
121drop index "&&star".OBS_FACT_CON_CODE_BI;
122whenever sqlerror exit;
123
124create bitmap index &&star.OBS_FACT_CON_CODE_BI
125 on &&star.observation_fact (CONCEPT_CD)
126 &&index_opts;
127
128
129whenever sqlerror continue;
130drop index "&&star".OBS_FACT_VALTYP_CD_BI;
131whenever sqlerror exit;
132
133create bitmap index &&star.OBS_FACT_VALTYP_CD_BI
134 on &&star.observation_fact (VALTYPE_CD)
135 &&index_opts;
136
137
138whenever sqlerror continue;
139drop index "&&star".OBS_FACT_TVAL_CHAR_BI;
140whenever sqlerror exit;
141
142create bitmap index &&star.OBS_FACT_TVAL_CHAR_BI
143 on &&star.observation_fact (TVAL_CHAR)
144 &&index_opts;
145
146
147whenever sqlerror continue;
148drop index "&&star".OBS_FACT_NVAL_NUM_BI;
149whenever sqlerror exit;
150
151create bitmap index &&star.OBS_FACT_NVAL_NUM_BI
152 on &&star.observation_fact (NVAL_NUM)
153 &&index_opts;
154 
155whenever sqlerror continue;
156drop index "&&star".OBS_FACT_MOD_CODE_BI;
157whenever sqlerror exit;
158
159create bitmap index &&star.OBS_FACT_MOD_CODE_BI
160 on &&star.observation_fact (MODIFIER_CD)
161 &&index_opts; 
162 
163whenever sqlerror continue;
164drop index "&&star".EM_ENCNUM_IDX;
165whenever sqlerror exit;
166
167create bitmap index &&star.EM_ENCNUM_IDX
168 on &&star.encounter_mapping (ENCOUNTER_NUM)
169 &&index_opts;
170 
171--Elapsed: 00:03:44.391
172 
173-- With parallel 12: 00:03:52.511
174-- With parallel option removed, >4 minutes
175
176
177-- Set the degree on our tables/indexes to allow parallelism (#1513)
178
179-- Tables
180ALTER TABLE &&star.observation_fact PARALLEL (DEGREE 2);
181ALTER TABLE &&star.concept_dimension PARALLEL (DEGREE 2);
182ALTER TABLE &&star.modifier_dimension PARALLEL (DEGREE 2);
183ALTER TABLE &&star.patient_dimension PARALLEL (DEGREE 2);
184ALTER TABLE &&star.provider_dimension PARALLEL (DEGREE 2);
185
186-- Indexes
187ALTER INDEX &&star.OBS_FACT_PAT_NUM_BI PARALLEL (DEGREE 2);
188ALTER INDEX &&star.OBS_FACT_CON_CODE_BI PARALLEL (DEGREE 2);
189ALTER INDEX &&star.OBS_FACT_ENC_NUM_BI PARALLEL (DEGREE 2);
190ALTER INDEX &&star.OBS_FACT_CON_CODE_BI PARALLEL (DEGREE 2);
191ALTER INDEX &&star.OBS_FACT_VALTYP_CD_BI PARALLEL (DEGREE 2);
192ALTER INDEX &&star.OBS_FACT_TVAL_CHAR_BI PARALLEL (DEGREE 2);
193ALTER INDEX &&star.OBS_FACT_NVAL_NUM_BI PARALLEL (DEGREE 2);
194ALTER INDEX &&star.OBS_FACT_MOD_CODE_BI PARALLEL (DEGREE 2);
195ALTER INDEX &&star.EM_ENCNUM_IDX PARALLEL (DEGREE 2);
196
197/* Removed the b-tree indexes for now - see above and also #1536, #1537
198ALTER INDEX &&star.FACT_NOLOB PARALLEL (DEGREE 2);
199ALTER INDEX &&star.FACT_CNPT_PAT_ENCT_IDX PARALLEL (DEGREE 2);
200ALTER INDEX &&star.FACT_PATCON_DATE_PRVD_IDX PARALLEL (DEGREE 2);
201*/
202
203/* Handy for cleaning up after a failed bulk-import to
204   the production observation_fact table:
205
206create index fact_upload_debug
207 on &&star.observation_fact
208 (upload_id);
209
210delete from
211&&star.observation_fact
212where upload_id=:upload_id;
213
214drop index fact_upload_debug;
215*/
216
217/** Handy for debugging duplicate key errors
218
219create index observation_fact_debug
220 on &&star.observation_fact
221 (ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM)
222 tablespace bheron_tables;
223
224select count(*), ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD
225               , INSTANCE_NUM
226from &&star.observation_fact
227group by ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD
228       , INSTANCE_NUM
229having count(*) > 1
230order by concept_cd;
231
232drop index observation_fact_debug;
233/* */
234
235/*
236alter index &&star."FACT_NOLOB" rebuild;
237alter index &&star."FACT_PATCON_DATE_PRVD_IDX" rebuild;
238alter index &&star.FACT_CNPT_PAT_ENCT_IDX rebuild;
239
240-- clue from http://www.orafaq.com/forum/t/70523/0/
241alter index idx_test unusable;
242alter index idx_test  rebuild;
243*/
244
Note: See TracBrowser for help on using the repository browser.