Because of the problems at the beginning of the table design, original want to insert the faster, the bottleneck is the query, the watch is too thin, cause most queries do joint query, A table (8 million), table B (8 million), C (8 million), is A one-to-one relationship, and partitioned,

Federated query a large number of these three tables, the problem of low lead to many now want to solve this problem, you have what idea?

I had materialized views and do the rowid, real-time commit updates,

But every time when the commit is slow, need about 5 s, can't accept, affect the base table updates, you are how to operate at ordinary times,
High real-time requirements, do not accept, more than 1 minute to refresh,

CodePudding user response:

First of all, don't use the refresh on the commit type of MV,

Your subsequent what kind of query in those join queries? Statistics class or small amount of data query? What is the typical SQL? I'm afraid you'll have to see the statistics itself should not be seconds out of SQL, may also need to make some trade-offs,