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/oracle_table_stats.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: 4.5 KB
Line 
1/** oracle_table_stats.sql -- Build Oracle table statistics
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
7These statements are to be run manually in SQL developer (or similar) after
8each ETL to build Oracle table statistics.  See tickets #1369, #845, and the
9HeronLoad wiki page (https://informatics.kumc.edu/work/wiki/HeronLoad).
10*/
11
12
13--Make sure we're in the de-id database
14select * from blueherondata.observation_fact where 1=0;
15
16--Unlock table statistics
17EXEC dbms_stats.unlock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'OBSERVATION_FACT');
18EXEC dbms_stats.unlock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'CONCEPT_DIMENSION');
19EXEC dbms_stats.unlock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'MODIFIER_DIMENSION');
20EXEC dbms_stats.unlock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'PATIENT_DIMENSION');
21EXEC dbms_stats.unlock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'PROVIDER_DIMENSION');
22EXEC dbms_stats.unlock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'VISIT_DIMENSION');
23
24--Build index statistics
25EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_ENC_NUM_BI', estimate_percent => 5, DEGREE => 6);
26EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_PAT_NUM_BI', estimate_percent => 5, DEGREE => 6);
27EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_CON_CODE_BI', estimate_percent => 5, DEGREE => 6);
28EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_VALTYP_CD_BI', estimate_percent => 5, DEGREE => 6);
29EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_TVAL_CHAR_BI', estimate_percent => 5, DEGREE => 6);
30EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_NVAL_NUM_BI', estimate_percent => 5, DEGREE => 6);
31EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBS_FACT_MOD_CODE_BI', estimate_percent => 5, DEGREE => 6);
32EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','SYS_IL0000067002C00016$$', estimate_percent => 5, DEGREE => 6);
33EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBSERVATION_FACT_UPLOAD_ID', estimate_percent => 5, DEGREE => 6);
34EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','OBSERVATION_FACT_PK', estimate_percent => 5, DEGREE => 6 );
35
36/* Removed the b-tree indexes for now - see above and also #1536, #1537
37EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','FACT_CNPT_PAT_ENCT_IDX', estimate_percent => 5, DEGREE => 6);
38EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','FACT_PATCON_DATE_PRVD_IDX', estimate_percent => 5, DEGREE => 6);
39EXEC DBMS_STATS.gather_index_stats('BLUEHERONDATA','FACT_NOLOB', estimate_percent => 5, DEGREE => 6);
40*/
41
42--Build table statistics
43exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"OBSERVATION_FACT"', ESTIMATE_PERCENT => 5, DEGREE => 6, CASCADE => false );
44exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"CONCEPT_DIMENSION"', ESTIMATE_PERCENT => dbms_stats.auto_sample_size, DEGREE => 6, CASCADE => false );
45exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"MODIFIER_DIMENSION"', ESTIMATE_PERCENT => dbms_stats.auto_sample_size, DEGREE => 6, CASCADE => false );
46exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"PATIENT_DIMENSION"', ESTIMATE_PERCENT => dbms_stats.auto_sample_size, DEGREE => 6, CASCADE => false );
47exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"PROVIDER_DIMENSION"', ESTIMATE_PERCENT => dbms_stats.auto_sample_size, DEGREE => 6, CASCADE => false );
48exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"VISIT_DIMENSION"', ESTIMATE_PERCENT => dbms_stats.auto_sample_size, DEGREE => 6, CASCADE => false );
49
50exec dbms_stats.gather_table_stats( OWNNAME => '"BLUEHERONDATA"', TABNAME => '"QT_PATIENT_SET_COLLECTION"', ESTIMATE_PERCENT => dbms_stats.auto_sample_size, DEGREE => 6, CASCADE => false );
51
52
53--Lock table statistics
54EXEC dbms_stats.lock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'OBSERVATION_FACT');
55EXEC dbms_stats.lock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'CONCEPT_DIMENSION');
56EXEC dbms_stats.lock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'MODIFIER_DIMENSION');
57EXEC dbms_stats.lock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'PATIENT_DIMENSION');
58EXEC dbms_stats.lock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'PROVIDER_DIMENSION');
59EXEC dbms_stats.lock_table_stats(ownname=>'BLUEHERONDATA', tabname=>'VISIT_DIMENSION');
Note: See TracBrowser for help on using the repository browser.