0

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.

Improve this question
3
  • Why do you want to create a secondary database for this?
    – Andrew Sayer
    Oct 12, 2021 at 11:59
  • You might want to contact an Oracle consultant to ask for suggestions regarding your wishes. Currently, your question could be solved with multiple solutions and as such is a kind of shopping list question. These aren't very suitable in a Q&A environment, where the community is looking for a specific solution to a specific issue. You might be wanting to look at Oracle Reporting Services or Oracle OLAP or even Oracle Hyperion. Hard to tell.
    – John K. N.
    Oct 26, 2021 at 9:27
  • Comments should only be used for asking for clarification, or to leave constructive criticism that guides the author in improving the post, or to add relevant but minor or transient information to a post (e.g. a link to a related question, or an alert to the author that the question has been updated), or to provide site usage guidance. See the help for details.
    – Hannah Vernon ♦
    Oct 26, 2021 at 12:24

2 Answers 2

Reset to default
1

The best option may be a Materialized View:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-MATERIALIZED-VIEW.html

There are a few methods for automatically updating the data, you should test/evaluate before choosing one or the other. ON COMMIT essentially works like a trigger would, but you don't need to worry about coding all of that on the back end (which may get complicated when multiple tables are involved).

Other things you can explore would be index-organizing the tables based on the GROUP BY columns. Depending on how much of the data is queried at a given point of time this can reduce the overhead of producing the aggregate results and you might not need to go through the trouble of other methods and could utilize regular views to ensure the correct aggregation is used.

Improve this answer
-2

You can use a Trigger in Oracle to transactionally update your aggregated statistics table every time a change occurs in your base table, automatically.

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.