-2

Would there be something similar as the master-slave database but at the table level in the database?

For example, I have the following scenario:

  1. I have a table with millions of records and the reason is because the system is more than 15 years old.
  2. I only want to show the records of the last year (2019-2020).
  3. I decided to create a view that only shows the records of that range (1 year) from the information of that table that contains millions of records.
  4. Thanks to the view, the loading time of that system screen is lighter, thanks to the fact that I have less load of records.

The problem: What if the user adds a new record to the table that contains millions of records? how do I make my view modify when the other table are modified ...

I can use triggers to update the view I think, but, is there a functionality in oracle that allows me something similar to what I just asked (master-slave) where the "slave" table is updated as the "master" table suffers changes?

TwoDent
  • 405
  • 7
  • 26

2 Answers2

1

First of all, you misunderstood views. View is not a physical table, and does not store any information. If you insert data into view, you are actually inserting into the source table.

Since the view is not physical, you are just filtering the data. This does not have any performance benefits.

For the big tables, you can use partitioning which drastically improves performance. And if you still need archival you can archive the partitioned data.

Partitioning is generally the best method, because you can typically archive data by simply doing an "exchange" command to archive off old data.

Data doesn't "move" in that scenario, it simply gets 'detached' from the table via data dictionary manipulation.

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
0

Would there be something similar as the master-slave database but at the table level in the database

If you are asking about master/slave replication on a table level, then, I suppose, table/materialized view relationship is appropriate to call as a master-slave. Quote from Oracle Docs:

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term)...

When you need to "update" or, more appropriately - refresh the mview, you can use different options:

Mview could be faster then view because each time you select from a mview you select from a different "table" which was replicated from the original one. Especially if you have complex logic in a sql, you can put the logic to mview definition.

The drawbacks are you need extra disk space for mview, and there will be a delay of refreshing the data.

Dmitry.M
  • 2,833
  • 1
  • 17
  • 30