search

CCRGeneticsBranch / Oncogenomics_v2

Oncogenomics portal version 2
0 stars 0 forks source link

Convert materialized views to tables on Oracle database #12

Open hsienchao opened 1 year ago

hsienchao commented 1 year ago

db_cleanup.sql.txt db_create_table.sql.txt

I cleaned up unused tables (113). All materialized views were dropped on dev. A new refreshView.pl was implemented.

hsienchao commented 1 year ago

I used VAR_AA_COHORT_MV as an example to test different refreshing strategies. Dropping and re-creating tables is fastest.

Drop Insert with Indexes Insert without Indexes Oracle Materialized View
205 secs 1032 secs 358 secs 265 secs
hsienchao commented 1 year ago

We can ask Jiqui to migrate all tables from oncosnpdev@fsitgl-oradb02t.ncifcrf.gov to MySQL now