i have a big problem and wear it in my brain many weeks.
I hope we can discuss about the following problem.
The database run under mandrake 7 linux with 2-4 CPUs with 1GB -2GB RAM.
Our system will build after the datawarehouse structures. That means fact tables and dimessions.
I have a table base (fact table). In this table there comes a lot of datarows between 500 MB and to 100 GB in the hour.
The Data which are coming in will load about the direct loader in the database.
But on the other hand there are catch to the base table because the data must be aggregated (there are pl/sql procedures) and than put to other report tables.
So you can see there is an contest between the direct load and the procedures.
So i think importent is that the contest to the table basde will be reduced.
Following Idees i have:::
1) I have another table check with have a flag and when the flag is set than the procedure will run and if the flag is not set than the loader can run now.
2) I will build the following situation: a star schema with
one fact table and a couple of dimension tables.
I'm filling in new data into the fact table (base table)and deleting
old data periodically from the fact table (that is, I'm
implementing some kind of ring buffer).
Also, there are MVs defined on the star schema.
The problem is to retain aggregated data in the MVs,
even after the corresponding detail data in the fact table
has been deleted.
The MV should aquire new data arriving in the fact table,
but should NOT "synchronize" with the deleted data.
3) To partitioned the base table. Fill one partion with the direct loader an hang this partition than into the base table.
so i have no contest between the filled partition from the direct loader and the right partition base table where the procedure can run.
4) Now my bad idee. I catch a day the data to the base table and in the night i will aaggregate the data and but them into a day table and the day table will the fact table of the star schema. But as i say it is the worest Idee because data stream goes in the night lower but not lower that i have no contest between the procedure and the direct load.
Can this be achieved?
Any help would be greatly appreciated.
I you have another questions to me, ask under email@example.com or we will see us here.