Need Help - a strategy for high volume inserts
656919Jan 15 2010 — edited Jan 21 2010Hi,
I have a database table that is experiencing a volume of 100K rows per hour. I'll detail more below, but the actual insert table, and a read-only table are used. It is taking about 45 minutes to move the data from the insert table to the read-only table. Too long for our needs.
I am looking for suggestions to tune/structure this amount of data inserts.
Here is the current architecture:
1. There are three tables:
Tab_RO = read only
Tab_A - used for inserts only
Tab_B - used for inserts only
2. A synonym named: Tab_RO_INSERT is pointed to either table Tab_A or Tab_B . THis makes the inserts go into either the A or B table. Every hour I run a schedule job that:
Scheduled Job -
a. Find which table is being used by the Synonym and store that in CURRENT_TABLE
b. synonym switch from A to B (or visa versa)
c. EXECUTE IMMEDIATE ' INSERT INTO Tab_RO SELECT * FROM ' || CURRENT_TABLE;
d. truncate CURRENT_TABLE: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || CURRENT_TABLE || ' ';
The goal was to alleviate the stress on the Tab_RO table so that useful queries could be performed, and cleanup maintenance could be performed (delete records older than x days). But as you can see the Scheduled Job is taking much longer than I thought.
Suggestions are much appreciated, thanks in advance.
M
Edited by: no_spam_2345 on Jan 15, 2010 10:38 AM