June 22, 2020 at 7:37 pm
Hi all,
in my datawarehouse i have large table with about 2.5B of rows. Table is partitioned by date. Each partition is one month and each has about 200M of rows. The table is constantly growing. Until now, i used this table only as history data and for reporting purpose i use tables with aggregated data, that i calculate with every new row that is added to this large table.
But now i need new aggregated table for new kind of report. And want to aggregate to the new table also all historical data from this huge table. When i run select, that should return required result for period longer than week, it takes lot of time. And i cant imagine how long it will takes for the whole table (even in batches).
My first thoughts was, that i could switch out partition to separate table and run select only against that, then switch it back, and repeat for all partitions. But partition is still data of one month. Is there any better solution that i could use? Aggregation select isnt complicated at all, i just want to get TOP 10 rows by amount for each month and customer. Thanks for any tips.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply