I hope I will get luck describing what I am looking for, because This is some thing new for me and I am not sure if I will use the correct names and terms.
I have tables which contains a million of data and thousands of new records daily. these tables can contains data like:
Classification, Location, Date, etc.
To query a data which require complex queries like location intersections/ joining, it takes long time, also we are trying to avoid using such queries on production DB.
I am thinking to create a new tables that will hold the count and statistics of these data. When record inserted in the main DB, it will be synched to second DB where all calculations will occur and save it into new tables, the tables can contains data like like:
Count of records in each location, count of records for each classification. count of record for each year/month/day/hour etc.
Table example could be like this
**Table name: Location:** LocationID Name Count 1 Arizona 3 *Table Name: DateCounts:* Year Month Day hour count 2021 10 1 5:30 4
The new data will be used for reporting and statistics.
I never worked in such thing and I am not sure the correct name and terms or technology that describe what I want to do.
Some one told me that Oracle have a ready software that do these things, but I could not found it.
So I am looking for the correct term/resources/links or software that can help me to achieve this and put me on the right path.