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/exc_init.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: 1.8 KB
Line 
1/** exc_init -- Create tables for ETL exceptions and fact loading.
2
3Copyright (c) 2014 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
7Ref: http://www.orafaq.com/node/76
8*/
9
10/* Capture exceptions when trying to insert into observation_fact. 
11See #1461
12*/
13
14create table NightHerondata.observation_fact_exceptions(
15  ora_err_number$   number,
16  ora_err_mesg$     varchar2(2000),
17  ora_err_rowid$    rowid,   
18  ora_err_optyp$    varchar2(2),
19  ora_err_tag$      varchar2(2000)
20  );
21
22
23/*
24
25Disable indexes.
26
27"An insert of a row into a simple table generates very very very
28little UNDO aka rollback (a delete ROWID message is put into rollback).
29
30An insert into an INDEXED table generates gobs of UNDO.  The index structure is modified heavily
31and the UNDO for these structural modifications is large"
32
33April 18, 2002
34http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2837676559353
35*/
36whenever sqlerror continue;
37alter table NightHeronData.observation_fact
38  nologging;
39alter table NightHeronData.observation_fact
40  disable constraint observation_fact_pk;
41drop index NightHeronData.observation_fact_pk;
42drop index NightHERONDATA.FACT_NOLOB;
43drop index NightHERONDATA.FACT_PATCON_DATE_PRVD_IDX;
44drop index NightHERONDATA.FACT_CNPT_PAT_ENCT_IDX;
45
46drop index NIGHTHERONDATA.observation_fact_upload_id;
47whenever sqlerror exit;
48
49
50/* Materialize the view in a temporary table. */
51whenever sqlerror continue;
52  drop table observation_fact_upload;
53whenever sqlerror exit;
54
55create table observation_fact_upload as
56select * from nightherondata.observation_fact where 1 = 0;
57alter table observation_fact_upload
58  add constraint observation_fact_pk
59  unique(ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD,INSTANCE_NUM);
60
Note: See TracBrowser for help on using the repository browser.