Hello everyone.
We use Oracle 11.2 on a Linux machine.
I have a very large table with millions of rows, lets call it "trx". Based on that table we build an aggregate table - "trx_hours", which is partitioned by hours. We also have a buffer table - trx_buffer. We fill the buffer table for a specific hour and then exchange it with a relevant partition. Everything works great but the transaction table gets updated very often. Every time it gets updated we rebuild all relevant hours. Recently we have added a few workers to be able to simultaneously rebuild multiplle hours. Since each worker calls the same procedure i had to create multiple copies of trx_buffer table, one for each worker. I also had to rewrite the code to use dynamic SQL to be able to insert into an appropriate buffer table to avoid contention. Now since its dynamic its very hard to maintain and add changes. The only way i could think of to work around the problem is to create additinional GTT buffer table, fill it with non-dynamic SQL and then have small dynamic sql that copies everything from GTT into an appropriate buffer table and exchange it. But that would add an additional overhead. Materialized view is not a solution either. They are very slow. Is there a better way?
before the change:
begin
insert into trx_buffer
(src_code
,dst_code
,amount)
select src_code
,dst_code
,sum(amount)
from trx
where trx.date >= to_date('01.01.2019','dd.mm.yyyy')
and trx.date < to_date('01.01.2019','dd.mm.yyyy') + 1 / 24
group by src_code
,dst_code;
execute immediate 'alter table TRX_HOURS exchange partition ' || l_part_name || ' with table trx_buffer';
end;
after the change
begin
execute immediate 'insert into ' || l_trx_buffer || '
(src_code
,dst_code
,amount)
select src_code
,dst_code
,sum(amount)
from trx
where trx.date >= to_date(''01.01.2019'',''dd.mm.yyyy'')
and trx.date < to_date(''01.01.2019'',''dd.mm.yyyy'') + 1 / 24
group by src_code
,dst_code';
execute immediate 'alter table TRX_HOURS exchange partition ' || l_part_name || ' with table ' || l_trx_buffer;
end;
Thank you in advance.