[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4688
  • Last Modified:

Need to rebuild materialized view

I have databases in two locations.
in db 'A' I have a table called PEOPLE
in db 'B' I have a materialized view called PEOPLE which is selecting rows from PEOPLE@'A'

I added a column to the PEOPLE table in db 'A'. I need the materialized view to reckognize this column.

What would be the best approach?

0
Contestoas
Asked:
Contestoas
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
should be easy with this:
http://www.ss64.com/ora/mview_a.html
ALTER MATERIALIZED VIEW PEOPLE REBUILD COMPILE;

Open in new window

0
 
Jinesh KamdarCommented:
ALTER MATERIALIZED VIEW people REBUILD COMPILE; errored out for me saying invalid option.

I believe for effecting a change in the columns within the underlying tables / views of an MV, you have to re-create the MV.
0
 
ContestoasAuthor Commented:
I got an error as well, but I managed to try both rebuild and compile separately and none of them worked anyway.

My databases are linked across a WAN, and with possibly unstable and slow network link between them. Therefore I hoped that there was a better approach to this.?

Is it possible to use dynamic sql inside an MW?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it possible to use dynamic sql inside an MW?
no.

>herefore I hoped that there was a better approach to this.?
I found that creating a materialized view on a materialized view could solve the issue, with the following logic:

* MV1 (on db2) is refreshed automatically from db1 to db2
* MV2 is refreshed by a job schedule, based on the fact if MV1 is valid AND if it has rows in it.
that way, MV2 is available with valid data to the user, and refreshing it from MV1 will be much faster

another, even more advanced approach would be this:

* MV1 (on db2) is refreshed automatically from db1 to db2, every second "day"
* MV2 (on db2) is refreshed automatically from db1 to db2, every other second "day"
* MV3 will be refreshed from the most recent of MV1 / MV2, using a job schedule.

a variant, to save the work for MV3 would be this:
instead of refreshing MV1 and MV2 automatically, use a job for that, which will update the oldest/invalid MV of the 2.
that job will then also update a NORMAL view to point on the most recent valid MV, after the MV has been updated.
that last method will be the most efficient AND with almost 0 user interruption to access the data. downside: it requires 2 times the stored as you have 2 MV...

0
 
Jinesh KamdarCommented:
>> I got an error as well, but I managed to try both rebuild and compile separately and none of them worked anyway.

That's b'coz u cannot change the underlying structure of an MV with an ALTER MATERIALIZED VIEW command. You will have to use the CREATE OR REPLACE MATERIALIZED VIEW command for that.
Re-create the MV by including the new table-column in the SELECT query.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now