Page History
...
The following Oracle SQL script contains statements to drop indexes from the OBSERVATION_FACT table (part 1 - to be inserted into an ETL pathway before fact loading) and to recreate the same indexes (part 2 - to be inserted into an ETL pathway after fact loading).
DROP TABLE "OBSERVATION_FACT" cascade constraints;
CREATE TABLE "OBSERVATION_FACT"
( note} "ENCOUNTER_NUM" NUMBER(38,0) NOT NULL ENABLE,
"PATIENT_NUM" NUMBER(38,0) NOT NULL ENABLE,
"CONCEPT_CD" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"PROVIDER_ID" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"START_DATE" DATE NOT NULL ENABLE,
"MODIFIER_CD" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"INSTANCE_NUM" NUMBER(18,0) NOT NULL ENABLE,
"VALTYPE_CD" VARCHAR2(50 BYTE),
"TVAL_CHAR" VARCHAR2(255 CHAR),
"NVAL_NUM" NUMBER(18,5),
"VALUEFLAG_CD" VARCHAR2(50 BYTE),
"QUANTITY_NUM" NUMBER(18,5),
"UNITS_CD" VARCHAR2(50 BYTE),
"END_DATE" DATE,
"LOCATION_CD" VARCHAR2(50 BYTE),
"OBSERVATION_BLOB" CLOB,
"CONFIDENCE_NUM" NUMBER(18,5),
"UPDATE_DATE" DATE,
"DOWNLOAD_DATE" DATE,
"IMPORT_DATE" DATE,
"SOURCESYSTEM_CD" VARCHAR2(50 BYTE),
"UPLOAD_ID" NUMBER(38,0),
CONSTRAINT "OBSERVATION_FACT_PK" PRIMARY KEY ("ENCOUNTER_NUM", "CONCEPT_CD", "PROVIDER_ID", "START_DATE", "MODIFIER_CD", "INSTANCE_NUM") USING INDEX LOCAL ENABLE
)
LOB ("OBSERVATION_BLOB") STORE AS BASICFILE (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION)
PARTITION BY RANGE ("CONCEPT_CD")
(
PARTITION BIO VALUES LESS THAN ('DEM'),
PARTITION DEM VALUES LESS THAN ('FAL'),
PARTITION FAL VALUES LESS THAN ('ICD'),
PARTITION ICD VALUES LESS THAN ('LAB'),
PARTITION LAB VALUES LESS THAN ('OPS'),
PARTITION OPS VALUES LESS THAN ('ORG'),
PARTITION ORG VALUES LESS THAN ('PAT'),
PARTITION PAT VALUES LESS THAN ('RAD'),
PARTITION RAD VALUES LESS THAN ('THE'),
PARTITION THE VALUES LESS THAN ('ZZZ')
);
CREATE INDEX of_ctx_blob ON observation_fact (observation_blob) indextype is ctxsys.context parameters ('sync (on commit)');
CREATE INDEX fact_nolob ON observation_fact (patient_num, start_date, concept_cd, encounter_num, instance_num, nval_num, tval_char, valtype_cd, modifier_cd, valueflag_cd, provider_id, quantity_num, units_cd, end_date, location_cd, confidence_num, update_date, download_date, import_date, sourcesystem_cd, upload_id) LOCAL;
CREATE INDEX fact_patcon_date_prvd_idx ON observation_fact (patient_num, concept_cd, start_date, end_date, encounter_num, instance_num, provider_id, nval_num, valtype_cd) LOCAL;
CREATE INDEX idrt_fact_cnpt_pat_enct_idx ON observation_fact (concept_cd, instance_num, patient_num, encounter_num) LOCAL;
CREATE INDEX idrt_fact_mdf_pat_enct_idx ON observation_fact (modifier_cd, instance_num, patient_num, encounter_num) LOCAL;
Code Block | ||||
---|---|---|---|---|
| ||||
-- 1. Drop OBSERVATION_FACT indexes DROP INDEX of_ctx_blob; DROP INDEX fact_nolob; DROP INDEX fact_patcon_date_prvd_idx; DROP INDEX idrt_fact_cnpt_pat_enct_idx; DROP INDEX idrt_fact_mdf_pat_enct_idx; -- 2. Recreate OBSERVATION_FACT indexes CREATE INDEX of_ctx_blob ON observation_fact (observation_blob) indextype is ctxsys.context parameters ('sync (on commit)'); CREATE INDEX fact_nolob ON observation_fact (patient_num, start_date, concept_cd, encounter_num, instance_num, nval_num, tval_char, valtype_cd, modifier_cd, valueflag_cd, provider_id, quantity_num, units_cd, end_date, location_cd, confidence_num, update_date, download_date, import_date, sourcesystem_cd, upload_id) LOCAL; CREATE INDEX fact_patcon_date_prvd_idx ON observation_fact (patient_num, concept_cd, start_date, end_date, encounter_num, instance_num, provider_id, nval_num, valtype_cd) LOCAL; CREATE INDEX idrt_fact_cnpt_pat_enct_idx ON observation_fact (concept_cd, instance_num, patient_num, encounter_num) LOCAL; CREATE INDEX idrt_fact_mdf_pat_enct_idx ON observation_fact (modifier_cd, instance_num, patient_num, encounter_num) LOCAL; |
Note |
---|
The script should be executed with the credentials of the schema containing the OBSERVATION_FACT table Please note that the script already contains both the optimized indexes as well as the LOCAL option for partitioning, which may need to be removed depending on availability and use of the partitioning feature. |
...