materialized view refresh

Posted on 2013-10-31
Last Modified: 2013-11-08
I have a couple of materialized views that i currently refresh on demand.

I need to have them automatically refreshed.

how can i alter these mviews without having to force a complete refresh when i alter?

i have a couple of views that i want to refresh on sundays, a couple that i want to refresh daily.

another question is, can i refresh multiple views at the same time?

say, i have 5, 3 gig tables that i want to refresh at 3am every night.

the source tables are on a mssql server.

will my bottle neck be processing power at the mssql server? or network bandwidth.

Question by:FutureDBA-
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Author Comment

ID: 39616609
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39616708
You can alter the view and provide a start and next time.

An example of this can be found in the link below:

The online docs should also have examples.

>>will my bottle neck be processing power at the mssql server? or network bandwidth.

We cannot answer this.  We don't know your system.


This does nothing.  There are no automated alerts that go out to Experts that are not already involved in the question by posting this.

Author Comment

ID: 39616725
We cannot answer this.  We don't know your system. <<

Source server = 4 quad core xeons with 16 gigs of ram.
Target ORacle server = 16 core VM with 32 gigs of ram

I can connect both boxes to a gig-e switch on the networking side.

This does nothing.  There are no automated alerts that go out to Experts that are not already involved in the question by posting this.<<

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 39616738
>>We cannot answer this.  We don't know your system.

Describing your hardware really doesn't help.  There are way to many factors involved for us to help.  You need to look at the amount of data that will be refreshed across the wire, how fast the disks/servers can process it, etc...

You can create smaller tables and set up some tests and attempt to forecast the final actual numbers.

For example:
Create a table with 100,000 rows, set up the materialized view, perform a percentage of DML that you believe will represent actual changes and time it.

Then create 200,000 rows, time it.


Then you can start to build a behavior that may allow you to forecast actual times and possibly predict where you may run into problems.

Just knowing the setup is sort of like asking:
I have a Ferarri with a top speed of 150 MPH.  How long will it take me to drive through Chicago and where will I be slowed down?

Author Closing Comment

ID: 39635030

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question