Page History
...
Code Block | ||||
---|---|---|---|---|
| ||||
DROP TABLE "OBSERVATION_FACT" cascade constraints; CREATE TABLE "OBSERVATION_FACT" ( "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'), -- "BIO" partition for biomaterials PARTITION DEM VALUES LESS THAN ('ENC'), -- "DEM" partition for demographics PARTITION ENC VALUES LESS THAN ('ICD'), -- "ENC" partition for encounter data PARTITION ICD VALUES LESS THAN ('LAB'), -- "ICD" partition for diagnoses PARTITION LAB VALUES LESS THAN ('OPS'), -- "LAB" partition for lab findings PARTITION OPS VALUES LESS THAN ('ORG'), -- "OPS" partition for procedures PARTITION ORG VALUES LESS THAN ('PAT'), -- "ORG" partition for organizational data PARTITION PAT VALUES LESS THAN ('RAD'), -- "PAT" partition for pathology findings PARTITION RAD VALUES LESS THAN ('THE'), -- "RAD" partition for radiology findings PARTITION THE VALUES LESS THAN ('ZZZ') -- "THE" partition for therapy data ); 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; |
Info |
---|
The script should be invoked with the user credentials of the schema containing the i2b2 ontology table. Partition ranges are defined with an "alphanumeric less-than" syntax, i.e. the BIO partition contains all concept codes up to (but not including) the prefix DEM, which starts the demographics partition. "ZZZ" is given as a dummy upper limit for the last partition, catching all alphanumeric prefixes after "THE" for the therapy partition. Please note that the fulltext index "OF_CTX_BLOB" cannot be partitioned (not supported by the database) and is thus created as a regular "global" index. All other indexes are partitioned using the "LOCAL" option. |